Switch names

[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

 

[Gord Dibben]

See Chip Pearson's site.

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


Gord Dibben MS Excel MVP

 

[T. Valko]

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]...

 

[Jacob Skaria]

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

 

[T. Valko]

If the convention is strictly Firstname Lastname:

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

 

[kathy at the front desk]

PERFECT!

Thanks!

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

 

[L. Howard Kittle]

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]...

 

[Teethless mama]

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