Need to separate and rearrange names

[Nadine]

How do you take Last,First and change it to First Last?
Ex: Joe,John needs to be rewritten to John Doe.
Using Excel 2003 and having the formula be in a different cell than the text
it is converting so that the original text remains unchanged.

 

[Gord Dibben]

See Chip Pearson's site for rearranging multiple permutations of names.

http://www.cpearson.com/excel/FirstLast.htm


Gord Dibben MS Excel MVP

 

[Bob Phillips]

Try

=TRIM(MID(A1,FIND(",",A1)+1,99))&" "&LEFT(A1,FIND(",",A1)-1)

 

[Rick Rothstein]

Assuming there is no space following the comma (both your examples show
that), give this formula a try...

=MID(A1&" "&A1,FIND(",",A1)+1,LEN(A1))

 

[Teethless mama]

=MID(A1&" "&A1,FIND(",",A1)+1,LEN(A1))

 

[Rick Rothstein]

Actually, if your data could be mixed (some with a space after the comma and
some without one), then you can use this formula to handle both of those
conditions...

=TRIM(MID(A1&" "&A1,FIND(",",A1)+1,LEN(A1)))

 

[Nadine]

Perfect! THank you so much.

Try

=TRIM(MID(A1,FIND(",",A1)+1,99))&" "&LEFT(A1,FIND(",",A1)-1)

--

HTH

Bob




.

 

[Nadine]

This leaves lots of extra space between the two names. Gord Dibben's formula
worked perfect. Thanks for taking a stab at it though. I appreciate it.

 

[Nadine]

This one leaves lots of spaces between the first and last name.

 

[Nadine]

That was supposed to say Bob Phillips' post worked perfect.

 

[Nadine]

This works. Thanks.

Actually, if your data could be mixed (some with a space after the comma and
some without one), then you can use this formula to handle both of those
conditions...

=TRIM(MID(A1&" "&A1,FIND(",",A1)+1,LEN(A1)))

--
Rick (MVP - Excel)




.

 

[Bob Phillips]

You were lucky, I was about to write to my MP <bg>