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

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

HTH
Kostis Vezerides

Try:

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


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=489070

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


"zomex" wrote:

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

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

--

HTH

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


"zomex" wrote in message
...
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

thank u very much... i really appreciate it

"vezerid" wrote:

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

HTH
Kostis Vezerides