Try one of these...

If all the names have single word last names:

Joe L Smith

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

If some last names may be more than one word:

Joe L La Cross

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",2))+1,50)

--
Biff
Microsoft Excel MVP


"Paul S" wrote in message
...
I have a spreadsheet of 400 names consisting of first name, middle initial
and surname (e.g. Joe x Blogs) and want to create a new column that just
gives me the surname. I'm sure that it can be done with the Right function
but just can't find the correct method to do it. Any help appreciated.
Thanks