extracting last name

G

Guest

I have names of individuals in Column A. Some have only first and last,
others also include the middle initial. I want to extract the last name of
each person into Column B. I used the formula =RIGHT(A1,LEN(A1)-SEARCH("
",A1)) which works great for those names that don't include the middle
initial. How can I modify this to work for either situation? Thanks!!!
 
N

N Harkawat

=MID(A1,LOOKUP(2,1/(MID(" "&A1,ROW(INDIRECT("1:1024")),1)="
"),ROW(INDIRECT("1:1024"))),1024)
 
R

Ron de Bruin

Try this with the nhame in D2

=IF(ISERR(FIND(" ",D2)),D2,MID(D2,FIND("^^",SUBSTITUTE(D2," ","^^",LEN(D2)-LEN(SUBSTITUTE(D2," ",""))))+1,1024))
 
D

Domenic

Try...

=RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Hope this helps!
 
G

Guest

Thanks Ron - that worked! Can you explain what the ^^ mean and why the 1024
at the end?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Separate first name, middle name and last name 2
parsing a full name out into Last, First, Middle 2
Vlookup and return sheet name also 2
Last Name 7
RIGHT not doing anything 3
Formula to Split First and Last Names 2
Question on breaking on cell into three seperate 1
parse text string 4

Top