Text string inverting

C

cagolden2003

I am importing text, and it comes in reading from right to left.
How can I change it to read from left to right?

I know that the formula below will invert two words, but how do you do more
than two words?

=RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1)

Let's say I wanted "Right To Left" to read "Left To Right" or "John J Doe"
to read "Doe J John"....
 
C

CLR

You could do Data > TextToColumns > using SPACE as the delimiter to separate
each word into it's own separate column..........then CONCATENATE them back
together into the strings as you wish..........

Vaya con Dios,
Chuck, CABGx3
 
R

Ron Rosenfeld

I am importing text, and it comes in reading from right to left.
How can I change it to read from left to right?

I know that the formula below will invert two words, but how do you do more
than two words?

=RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1)

Let's say I wanted "Right To Left" to read "Left To Right" or "John J Doe"
to read "Doe J John"....
Click to expand...

You can do that with a UDF.

<alt><F11> opens the VB Editor.

Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens.

You can then use the function =RevWords(cell_ref) in any cell and it should do
what you describe. This requires a recent enough version of Excel that you
have VBA6. If you have an older version of Excel, we will have to substitute
for the Join and Split functions.

======================
Function RevWords(str As String) As String
Dim t1() As String, t2() As String
Dim l As Long, i As Long

t1 = Split(str)
ReDim t2(UBound(t1))

For l = UBound(t1) To 0 Step -1
t2(i) = t1(l)
i = i + 1
Next l

RevWords = Join(t2)

End Function
=========================
--ron
 
G

Gerrit-Jan Linker

Perhaps the functions in my litLIB Excel functions library can help.

See:
http://www.oraxcel.com/projects/litlib/
and
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1133444908

To swap two words you could do the following:

A1: Joe Bloggs

A3: =Concatenate( WordsRight(A1,1), " ", WordsLeft(A1,1))

If you have more than two words you can use the WordsMid function:

A1: "This is great"

A3: = Concatenate( WordsMid(A1,3,1), " ", WordsMid(A1,2,1), " ",
WordsMid(A1,1,1) )

Hope this helps,

Gerrit-Jan Linker
www.oraxcle.com
 
Top