Morning, I have an extensive list of names that I need to split into forenames and surnames.
I have attached a sample spreadsheet.
I have tried to use the text - to - columns function but this splits each name into a separate cell which is not what I am looking for.
To clarify I want
Mr John Paul Murphy to become
Mr
John Paul
Murphy
in the same row
Any ideas?
Thanks
Last edited by guerillaexcel; 07-06-2011 at 06:18 AM.
Or, if i can just extract the surname then that would be helpful? Possibly a formula with =RIGHT?
Try these:
In B1 copied down: =LEFT(A1,SEARCH(" ",A1)-1)
In C1 copied down: =TRIM(MID(A1,SEARCH(" ",A1),LEN(A1)-LEN(B1)-LEN(D1)))
In D1 copied down: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),50))
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Hi Dom, that works perfectly for all names with a title, apologies for scope creep here, but how do i handle those names that don't have a mr or mrs in their name?
e.g.
John P Butler
Mary Anne Lloyd-Evan
Last edited by guerillaexcel; 07-04-2011 at 06:09 AM. Reason: adding examples
Bump. Anyone able to help?
Try:
In B1: =IF(ISNUMBER(MATCH(LEFT(A1,SEARCH(" ",A1)-1),{"Mr","Mrs","Miss","Ms"},0)),LEFT(A1,SEARCH(" ",A1)-1),"")
In C1: =TRIM(MID(A1,LEN(B1)+1,LEN(A1)-LEN(B1)-LEN(D1)))
In D1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),50))
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
You are a gentleman and scholar Dom! That's fantastic, thanks very much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks