Last Name

[Guest]

Hi,

I have hundreds of cell containing full names starting with their first
name. I need a formula to display Last Name, First name and Middle Initial.
Ex: Johnny K. Walker should be Walker, Johnny K.

Thxs,

 

[Niek Otten]

Look here:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
|
| I have hundreds of cell containing full names starting with their first
| name. I need a formula to display Last Name, First name and Middle Initial.
| Ex: Johnny K. Walker should be Walker, Johnny K.
|
| Thxs,

 

[Guest]

Thx for the reply, but the link did not help me with my question. It require
the info to already be by last name. I need a formula that would show last
name first, first name and middle from a cell that begin with the first to
last name.

 

[Guest]

Assuming full names always included middle initial, then try this:

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

 

[Guest]

Thx for the reply, full name does not always have the middle initial.

 

[Peo Sjoblom]

What's the difference? It's not like any formula can distinguish between a
first and a last name. All the formulas do is to reverse the order of the
text strings and that is what you want to do, right? Also you should really
post an example how the names look like, do you for instance have commas or
just spaces, if the latter you can replace "," with " "

 

[Guest]

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

 

[T. Valko]

Some of your results end with char(32). Try this, it's shorter:

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

If you have something like this it will be ok:

Oscar de la Hoya (boxer)

But, if you have something like this:

Oscar T. de la Hoya (boxer)
Martin St. Louis (NHL hockey player)

Good luck!

There are just *too many* possibilities to try to account for.

Biff