Pull Out Last Name
 

Hi:

I am looking for a function in Excel to extract the LAST NAME from a cell
that looks like:

Justin Kenney

I want the formula to give me only the last name (ie Kenney)

Can anyone help with a formual (maybe the RIGHT function) that looks RIGHT
TO LEFT for the 1st space...and then gives me everything to the RIGHT of the
space

Thanks
Warren

Pull Out Last Name
 

One way:

=TRIM(MID(A1,FIND(" ",A1&" "),255))

Biff

"WDP" wrote in message
...
Hi:

I am looking for a function in Excel to extract the LAST NAME from a cell
that looks like:

Justin Kenney

I want the formula to give me only the last name (ie Kenney)

Can anyone help with a formual (maybe the RIGHT function) that looks RIGHT
TO LEFT for the 1st space...and then gives me everything to the RIGHT of
the
space

Thanks
Warren

Pull Out Last Name
 

WDP wrote:
Hi:

I am looking for a function in Excel to extract the LAST NAME from a
cell that looks like:

Justin Kenney

I want the formula to give me only the last name (ie Kenney)

Can anyone help with a formual (maybe the RIGHT function) that looks
RIGHT TO LEFT for the 1st space...and then gives me everything to the
RIGHT of the space

Thanks
Warren


Hi Warren,

I think you can use this formula:

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

that take in account the possibility to have more than one space per cell.
If you are sure that there is just one space per cell you can use this one:

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


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Pull Out Last Name
 

On Thu, 24 Aug 2006 18:17:01 -0700, WDP wrote:

Hi:

I am looking for a function in Excel to extract the LAST NAME from a cell
that looks like:

Justin Kenney

I want the formula to give me only the last name (ie Kenney)

Can anyone help with a formual (maybe the RIGHT function) that looks RIGHT
TO LEFT for the 1st space...and then gives me everything to the RIGHT of the
space

Thanks
Warren

This formula will pull out everything after the last space in the string:

=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1," ",
CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),255)

If there might be a trailing <space in the string, then use:

=MID(TRIM(A1),1+FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",
CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")))),255)


--ron