FLIP or Interchange or Swap words in any language

[NJ786]

Hi


can any one help on the following (right to left - Arabic), but would prefer a general formula for any language type.


Original Text

المسلسل التاريخي - أسعد الوراق


Final Result

المسلسل التاريخي - أسعد الوراق


The above has to be true for any case, if we have 2 words, 3 words, 4 words, sometimes we even have more than 10 words.


Any help will be appreciated.


NJ

 

[Luke M]

The easiest way is to use the ReverseCell udf. Should work for any language.

http://www.ozgrid.com/VBA/ReverseCell.htm


As I can not read your example unfortunately, I'm guessing that this is what you were after.

 

[SirJB7]

@Luke M

Hi!

Just gonna give a look at this link, maybe it helps me in a project I'm involved in. Thank you a lot!

Regards!


... EDIT:

Looked yet but I had done that already. Thanks anyhow!

 

[dmthornton]

Thought I'd throw out an idea for others to elaborate on. Haven't looked into the reversecell udf, so forgive me if this is the same logic.


How about a udf using the split function? It creates an array that you could loop through in reverse. The tricky part would be the delimiter. From what I'm guessing your example has two words? How are they identified (delimiter)?

 

[Luke M]

@SirJB7


Here's two other ways of flipping words around. Starting with "This is a test", if you want:


sihT si a tset

http://www.daniweb.com/software-development/vbnet/threads/341754/reverse-a-string-word-by-word

test a is This

[pre]

'Use the Function FlipWords in your worksheet
Function FlipWords(s As String)
x = Len(s) - Len(WorksheetFunction.Substitute(s, " ", ""))

If x > 0 Then
For i = 1 To x + 1
FlipWords = FlipWords & ReturnLastWord(s) & " "
s = Trim(Left(s, Len(s) - Len(ReturnLastWord(s))))
Next
FlipWords = Trim(FlipWords)
End If
End Function
Function ReturnLastWord(The_Text As String)
Dim stGotIt As String

If Len(The_Text) - Len(WorksheetFunction.Substitute(The_Text, " ", "")) > 0 Then
stGotIt = StrReverse(The_Text)
stGotIt = Left(stGotIt, InStr(1, stGotIt, " ", vbTextCompare))
ReturnLastWord = StrReverse(Trim(stGotIt))
Else
ReturnLastWord = The_Text
End If

End Function

[/pre]

 

[SirJB7]

@Luke M


Hi!


Yet copied and tested, now thank you a lot.

But my main issue about this is with texts in RTL (right to left) languages, like hebrew and arabic, and better I don't mention strings like "text-in-normal-language / text-in-RTL-language" with text in both systems (used for equivalence or traductions...).


I've never faced something so strange at first sight, where the visual component tends to lead you to a wrong conclusion, but when you get deeper it's so logic.

If you want to play around a little with those texts, give a look to this file, it's just a sample:

http://dl.dropbox.com/u/60558749/Example%20of%20RTL%20languages%20mixed%20strings%20handle.xlsx

... and try to guess (visually first) where the string begins and then which is the "path" to where it ends (when you give up, edit the cell, press F2, Home, and then right cursor... or it was the left? hahaha!): column A is the introductory step and column D is the advanced.


Good luck, good Luke!


Regards!