Right function
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 |
Right function
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 |
Right function
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 |
Right function
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 |
All times are GMT +1. The time now is 02:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com