extract last name only

mcgonma · Mar 5th 2012

I'm looking to pull into Column B the last name only from the "full name" column (column C).
Right now, in Column B I have the formula =UPPER(MID(C3,FIND(" ",C3)+1,50)) to pull in the last name from the full name that is in column C.
The problem is that when column C has a middle initial or a nickname, column B ends up having the middle initial and the last name.
For example, if Column C has John "Buster" Jones, I get "Buster" Jones in column B but I only want Jones; or if Column C has Mary T. Smith, I get T. Smith in Column B but I only want SMITH
Is there a better approach so I only get the last name?

jproffer · Mar 5th 2012

Re: extract last name only

This should get just the last name regardless of middle initial or middle name.

=TRIM(IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1)),MID(A1,FIND(" ",A1),9999),MID(A1,FIND(" ",A1,FIND(" ",A1)+1),9999)))

**PCI** · Mar 5th 2012

Re: extract last name only

A formula coming from this forum once in the past.
A1 = your data
B1 = TRIM(REPLACE(A1,1,FIND("^^",SUBSTITUTE(TRIM(A1)," ","^^",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")))),""))

snb · Mar 5th 2012

Re: extract last name only

or a UDF

Code

function lastname(c01)
  lastname=split(c01)(ubound(split(c01)))
end function

in B1:

= lastname(A1)

Armando Montes · Mar 5th 2012

Re: extract last name only

Maybe:
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

extract last name only

Participate now!

Share