Seperate First, Middle and Last name to seperate columns
Hi!
i use MS Office 2007
I have a column A containing FULL NAMES
(which consists of :
1. First name OR
2. First Name and Last Name OR
3. First Name, Middle and Last Name)
I would be grateful if i could get the results in separate columns (i.e. column B= First Names; column C= Middle [return N/A - if MIDDLE not present]; column D= Last Name)
appreciate your early reply
First nameMiddle
=LEFT(A4,FIND(" ",A4)-1)Last=IFERROR(MID(A4,FIND(" ",A4)+1,FIND("*",SUBSTITUTE(A4," ","*",2))-FIND(" ",A4)-1),"")=IF(ISERR(FIND("*",SUBSTITUTE(A4," ","*",2))),MID(A4,FIND(" ",A4)+1,LEN(A4)),RIGHT(A4,LEN(A4)-FIND("*",SUBSTITUTE(A4," ","*",2))))
Ben Van Johnson
Or just use Text2Columns, using space as the delimiter
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Note: ProtonLeah's first formula needs an IFERROR to cater for a single name, that is, no space present.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
@Ford: I had thought of suggesting that but it doesn't cope with a missing middle name.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks