Formula help...right/find command

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 can’t get it to work. Thanks.
 
=RIGHT(A1,LEN(A1)-FIND(" ",A1))

BUT only there are two names: Cliff M Lee will return "M Lee"
 
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
 
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)
Click to expand...

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
 
Back
Top