reverse order of text in a single cell
Posted by Susan Cincotti on September 19, 2001 8:40 AM
is there any way to create a function that removes everything rigth of the space in a cell that contains last name, first name? I need to change "Doe, John to "John Doe" for 30,000 cells. There has got to be simpler way than re-typing.
Posted by Tom Urtis on September 19, 2001 8:56 AM
Try this formula
Susan,
Assuming your list of names begins in A2, in B2 enter this formula and copy down as needed:
=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2, " ","")))))&" "&LEFT(A2,FIND(" ",A2)-1)
Tom Urtis
Posted by Aladin Akyurek on September 19, 2001 9:05 AM
Susan,
Assuming that the full names are all of the type "LastName, FirstName", you can use
In B1 enter: =RIGHT(A1,LEN(A1)-SEARCH(",",A1)-1)&" "&LEFT(A1,SEARCH(",",A1)-1)
Copy down as needed.
Select all the cells of B and do a Edit|Paste Special|Values over the original values in A then delete column B.
By the way, this question came up a few times at this board. Another approach consists of using Data|Text To Columns (see the relevant threads).
Aladin
Posted by Eric on September 19, 2001 9:06 AM
another way- text to columns
Select the list of names, go Edit-->text to columns
in the dialogue make sure the delimited radio button is selected, click next, check comma as the delimiter, and click finish
That leaves the last name in col (A) and the first name in col (B), and gets rid of the comma.
Then enter the formula
=TRIM(b2)&" "&a2
in c2 and copy down as needed.
Hope that helped
