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

See Chip Pearson's site.

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


Gord Dibben MS Excel MVP

On Tue, 24 Nov 2009 10:53:02 -0800, kathy at the front desk <kathy at the
front wrote:

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
wrote in message
...
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

If the convention is strictly Firstname Lastname:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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
wrote in message
...
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

Use a helper column with the below formula to reverse the names..Once done
copypaste specialvalues 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
---------------
Jacob Skaria


"kathy at the front desk" wrote:

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

PERFECT!

Thanks!

"Jacob Skaria" wrote:

Use a helper column with the below formula to reverse the names..Once done
copypaste specialvalues 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
---------------
Jacob Skaria


"kathy at the front desk" wrote:

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

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
wrote in message
...
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

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


"kathy at the front desk" wrote:

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