Last Name
 

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,

Last Name
 

Look he

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

Last Name
 

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.

"Niek Otten" wrote:

Look he

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

Last Name
 

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))


"Talladega" wrote:

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,

Last Name
 

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

"Teethless mama" wrote:

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))


"Talladega" wrote:

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,

Last Name
 

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 " "


--
Regards,

Peo Sjoblom

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

"Niek Otten" wrote:

Look he

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

Last Name
 

=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))


"Talladega" wrote:

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

"Teethless mama" wrote:

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))


"Talladega" wrote:

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,

Last Name
 

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

"Teethless mama" wrote in message
...
=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))


"Talladega" wrote:

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

"Teethless mama" wrote:

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))


"Talladega" wrote:

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,