In cell N92 there is a name (Cliff Lee). I would like a formula in cell Z92
to get the last name (Lee) from cell N92. I have more names in column N that
I want to do the same thing to. What formula should I use? I am trying to
use the right and find commands but I cant get it to work. Thanks.

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

BUT only there are two names: Cliff M Lee will return "M Lee"

"Jambruins" wrote:

In cell N92 there is a name (Cliff Lee). I would like a formula in cell Z92
to get the last name (Lee) from cell N92. I have more names in column N that
I want to do the same thing to. What formula should I use? I am trying to
use the right and find commands but I cant get it to work. Thanks.

thanks.

"Toppers" wrote:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

BUT only there are two names: Cliff M Lee will return "M Lee"

"Jambruins" wrote:

In cell N92 there is a name (Cliff Lee). I would like a formula in cell Z92
to get the last name (Lee) from cell N92. I have more names in column N that
I want to do the same thing to. What formula should I use? I am trying to
use the right and find commands but I cant get it to work. Thanks.

This extract the lat name even if there is a middle name or initial.

=MID(A1,FIND("^^",SUBSTITUTE(" "&A1," ","^^",LEN(" "&A1)-LEN(SUBSTITUTE("
"&A1," ","")))),999)

It falls over if the last name has more than 999 leter3 in it :)

Mike

"Jambruins" wrote:

In cell N92 there is a name (Cliff Lee). I would like a formula in cell Z92
to get the last name (Lee) from cell N92. I have more names in column N that
I want to do the same thing to. What formula should I use? I am trying to
use the right and find commands but I cant get it to work. Thanks.

This extract the lat name even if there is a middle name or initial.

But, of course, it will not solve the problem of someone having a two-part
name. I have a friend whose name is Frank Della Rossa... Della Rossa is his
last name. By the way, I am not faulting your formula at all (I've post a
similar one in the past to slice off the last word in a list), there is no
single "rule" that can be applied to names that will work in all
situations... it is just the nature of names.

Rick

This extract the lat name even if there is a middle name or initial.

=MID(A1,FIND("^^",SUBSTITUTE(" "&A1," ","^^",LEN(" "&A1)-LEN(SUBSTITUTE("
"&A1," ","")))),999)

Might I suggest this slight modification to make the Formula survive
trailing blanks...

=MID(TRIM(A1),FIND("^^",SUBSTITUTE(" "&TRIM(A1)," ","^^",LEN("
"&TRIM(A1))-LEN(SUBSTITUTE(""&TRIM(A1)," ","")))),999)

Rick