Text string inverting

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"....

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



"cagolden2003" wrote:

> 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"....

On Thu, 26 Jan 2006 11:16:02 -0800, cagolden2003
<[email protected]> wrote:

>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"....

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

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...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