HOW TO RETRIVE LAST NAME USING EXCEL, IT SHOULD BE GENERIC

[Guest]

if the names given are
peter alan smith
geroge w bush
william scott scotty

than how can i retrive the last name, in another cell... like for the
retreval of first name the formula =LEFT(E5, FIND(" ",E5)) would do the
trick, but cant seem to figure how to retreve the last name...
thanks

 

[vezerid]

=MID(E5, FIND(" ", E5, FIND(" ", E5)+1), LEN(E5)

HTH
Kostis Vezerides

 

[Vito]

Try:

=RIGHT(A1,LEN(A1)-SEARCH("#",SUBSTITUTE(A1&" "," ","#",2))

 

[Guest]

If the number of spaces may vary (George Bush vs George W. Bush) Try this:
=RIGHT(A14,MATCH("
",MID(A14,LEN(A14)+1-ROW(INDIRECT("1:"&(LEN(A14)))),1),0)-1)

Note: commit that array formula by holding down [Ctrl]+[Shift] when you
press[Enter]

Does that help?

***********
Regards,
Ron

 

[Bob Phillips]

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

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

thank u very much... i really appreciate it