Formula help...right/find command

J

Jambruins

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.
 
T

Toppers

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

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

Mike H

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
 
R

Rick Rothstein \(MVP - VB\)

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
 
R

Rick Rothstein \(MVP - VB\)

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
 
Top