Switch names

  • Thread starter Thread starter kathy at the front desk
  • Start date Start date
K

kathy at the front desk

I have a name list that is first name then last name in same cell. Is there a
way to make it last name then first name? Same cell would be great. Thanks
 
One way using a formula in a helper column but with limitations...

Will work on names like:

John Smith
John L. Smith

Will not work on names like:

John Smith Jr.
Oscar De La Hoya

All on one line:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",20)),20))
&" "&LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(
SUBSTITUTE(A1," ",REPT(" ",20)),20)))-1)

--
Biff
Microsoft Excel MVP


"kathy at the front desk" <kathy at the front
(e-mail address removed)> wrote in message
news:[email protected]...
 
Use a helper column with the below formula to reverse the names..Once done
copy>paste special>values and remove the current column

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

You will require some find tuning to handle single names...

If this post helps click Yes
 
If the convention is strictly Firstname Lastname:

=MID(A1,FIND(" ",A1)+1,20)&" "&LEFT(A1,FIND(" ",A1)-1)
 
PERFECT!

Thanks!

Jacob Skaria said:
Use a helper column with the below formula to reverse the names..Once done
copy>paste special>values and remove the current column

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

You will require some find tuning to handle single names...

If this post helps click Yes
Click to expand...
 
Try this, where there is a comma seperating the last and first names.

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

or

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

Which does the same thing.

HTH
Regards,
Howard

"kathy at the front desk" <kathy at the front
(e-mail address removed)> wrote in message
news:[email protected]...
 
Back
Top