Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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".... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text string inverting
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".... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text string inverting
On Thu, 26 Jan 2006 11:16:02 -0800, 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".... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text string inverting
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text string and sum | Excel Worksheet Functions | |||
Remove last character of text string | Excel Worksheet Functions | |||
dynamic cell reference within a text string | Excel Worksheet Functions | |||
Extracting from a text string | Excel Worksheet Functions | |||
Formating a text string? | Excel Discussion (Misc queries) |