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

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

Hi Paul,

Hopefully this quick answer will help you out.

Use the 'Text to Columns' function
Assuming you have a space between the first/middle/last names

Highlight the data in question,
select 'text to column'
tick the box 'delimited' ...next
tick the box 'space' ... next & finish

Hope this helps
Cheers
Paul






"Paul S" wrote:

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

The following formula will handle names with and without middle initials (my wife doesn't have a middle name so if you had her name in your data, there would only be two parts to her name, not three)...

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

--
Rick (MVP - Excel)


"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