» Reversing Strings
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
Converting the full names in column A, formatted "LastName, FirstName" into names formatted "FirstName LastName".
Solution:
Use the RIGHT, LEFT, LEN and FIND text category functions, as follows:
=RIGHT(A2,LEN(A2)-FIND("","",A2)-1)&"" ""&LEFT(A2,FIND("","",A2)-1)
Example:
Full Name_________Result
Seinfeld, Jerry___Jerry Seinfeld
Bush, George______George Bush
Jordan, Michael___Michael Jordan
Bowie, David______David Bowie
Book Store:
Reversing String
Mike
This doesn't work
standinwave
I just removed the double quotes within the FIND function and it worked fine - like so =RIGHT(A2,LEN(A2)-FIND(",",A2)-1)&" "&LEFT(A2,FIND(",",A2)-1)
vikkam
How do I seperate the one string to 2 columns
Bush, George to George (column a) Bush (column b)
jasoncw
Data > Text to Columns, then make sure both space and comma are checked. To reverse the columns you can cut and paste.
vikkam
I am still confused.
can you please elaborate by giving example
daddylonglegs
A formula approach......
with "Bush, George" in A1 use this formula in B1
=TRIM(REPLACE(A1,1,FIND(",",A1&","),""))
and in C1
=LEFT(A1,FIND(",",A1&",")-1)
VBA Noob
Try
In B1
=RIGHT(A1,FIND(",",A1)+1)
and
in C1
=LEFT(A1,FIND(",",A1)-1)
Then paste special values in A1 and B1
VBA Noob
jasoncw
Ok, say the names are in cells A1:A10. Highlight these cells, then from the menu select Data, then Text to Columns. This will bring up the Convert Text to Columns Wizard.
Select Delimited, then click Next.
Make sure both "Space" and "Comma" check boxes are checked, then click Finish.
What this will do is put the last names in cells A1:A10 and first names in cells B1:B10. If you want them in reverse order, you can simply cut and paste the cells to where you would like them.
vikkam
thanks
vikkam
What if there is a middle initial?
lradon
How can the formula be adjusted if the name read:
Smith, Brian A.
TIA, Laura :)
jasoncw
[QUOTE=lradon]How can the formula be adjusted if the name read:
Smith, Brian A.
TIA, Laura :)[/QUOTE]
Assuming the name is in A1,
B1 (first name):
[code]=IF(RIGHT(A1,1)=".",MID(A1,FIND(" ",A1,1)+1,LEN(A1)-FIND(",",A1,1)-4),RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))[/code]
C1 (middle initial):
[code]=IF(RIGHT(A1,1)=".",MID(A1,LEN(A1)-1,1),"")[/code]
D1 (last name):
[code]=LEFT(A1,FIND(",",A1,1)-1)[/code]
Thanks Jason
lradon
I will try it!! :)
Miguel
And you must change some commas like the following:
=RIGHT(A2;LEN(A2)-FIND(",";A2)-1) & " " & LEFT(A2;FIND(",";A2)-1)
