Problem:
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
|
Reversing String
Mike wrote on December 31, 1969 19:00 EST |
| This doesn't work |
standinwave wrote on April 20, 2006 10:33 EST |
| 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 wrote on January 17, 2007 17:26 EST |
How do I seperate the one string to 2 columns
Bush, George to George (column a) Bush (column b) |
jasoncw wrote on January 17, 2007 17:30 EST |
| Data > Text to Columns, then make sure both space and comma are checked. To reverse the columns you can cut and paste. |
vikkam wrote on January 17, 2007 17:32 EST |
I am still confused.
can you please elaborate by giving example |
daddylonglegs wrote on January 17, 2007 17:34 EST |
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 wrote on January 17, 2007 17:34 EST |
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 wrote on January 17, 2007 17:35 EST |
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 wrote on January 17, 2007 18:07 EST |
thanks
vikkam |
What if there is a middle initial?
lradon wrote on January 18, 2007 12:13 EST |
How can the formula be adjusted if the name read:
Smith, Brian A.
TIA, Laura :) |
jasoncw wrote on January 18, 2007 13:32 EST |
[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 wrote on January 18, 2007 13:44 EST |
| I will try it!! :) |
Miguel wrote on December 31, 1969 19:00 EST |
And you must change some commas like the following:
=RIGHT(A2;LEN(A2)-FIND(",";A2)-1) & " " & LEFT(A2;FIND(",";A2)-1) |
|
The 22 Immutable Laws of Marketing : Exposed and Explained by the World's Two
