Finding last name in a first last name string

I have a name field that contains first middle lastname. I want to extract the lastname and put it in another column. I think what I need is a function to return everything on the right hand side of the field from the end back to the first space. Can't seem to find the correct function. Any clues?

Try this:

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

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Brian Bonner" wrote in message
...
I have a name field that contains first middle lastname. I want to extract
the lastname and put it in another column. I think what I need is a function
to return everything on the right hand side of the field from the end back
to the first space. Can't seem to find the correct function. Any clues?

Hi
try
=MID(A1,FIND(" ",A1)+1,255)

also have a look at
http://www.cpearson.com/excel/FirstLast.htm


--
Regards
Frank Kabel
Frankfurt, Germany


Brian Bonner wrote:
I have a name field that contains first middle lastname. I want to
extract the lastname and put it in another column. I think what I
need is a function to return everything on the right hand side of the
field from the end back to the first space. Can't seem to find the
correct function. Any clues?

Thanks for your reply. The function works if the field contains only firstname lastname. Unfortunately the field may contain firstname middlename and firstname middlename lastname so it has to find the very last space in the string and then consider what follows the lastname. I will check out the cpearson site as well.

Hi
sorry forgot your middle name. Try
=MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,255)


--
Regards
Frank Kabel
Frankfurt, Germany


Frank Kabel wrote:
Hi
try
=MID(A1,FIND(" ",A1)+1,255)

also have a look at
http://www.cpearson.com/excel/FirstLast.htm



Brian Bonner wrote:
I have a name field that contains first middle lastname. I want to
extract the lastname and put it in another column. I think what I
need is a function to return everything on the right hand side of
the
field from the end back to the first space. Can't seem to find the
correct function. Any clues?