Taken from: http://www.ozgrid.com/Excel/extract-words.htm :
----------------------

Get Last Word From Text String. See Also: Extract Last Word VBA

Text Sting in A2 is;
Our main business focus is Excel Spreadsheets

Formula/Function is;
=MID(SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2,"
",""))),FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2,"
",""))))+1,256)
Formula/Function result is;
Spreadsheets
---------------------------

Of course, those with Jr and the like at the end will be a problem

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"QDZF" wrote in message
...
I have one cell that contains first name(s) and last name. I want to break
the last name out and use the remaining data for first name. I can't do a
simple text to column because the column has data like:

John Smith
Joe & Jane Doe Jr.
Paul P Smith

I thought an easy way to do this would be to use a formula that would
(going
from the right) give me the text until it hits a space. I could then
count
the length of the parsed data and back that off of the origianl cell to
get
first and last name columns. I think this would work but it's not ideal.
Is
there a better solution?