Finding the final word in a string

[IainT]

Hey guys,

I was wondering if I could ask you kind people for help.

I have a field containing a full name which contains information like:

Mr Alan James McLellan
Mrs S Cowans
Mrs Elaine Thompson
Mrs Iain S Duncan
Mrs J White
Dr David Jameson
Miss A L Rankin

I need to extract the surname of each of these people in another cell but can't for the life of me figure out how. They could have anything up to 4 or 5 middle names so there is no consistency to the number of names.

I feel that this should be extremely easy but I'm stumped. If anyone has the magic formula or VBA code which I'm missing I would be extremely grateful.

Thanks
Iain

[VBA Noob]

Try this link

http://www.ozgrid.com/VBA/ExtractLastWord.htm

or

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

VBA Noob

[IainT]

Absolutely spot on! Thank you kindly. Couldn't have asked for more.



IainT

[daddylonglegs]

Here's another alternative

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99))

[VBA Noob]

Nice one Daddylonglegs

VBA Noob