Hi Can anybody help with this problem I have a text field in A1 which reads
Mr James John Smith
Is there a way to move this data into seperate columns
Mr 1st Name 2nd Name Surname
Just to make it more complicated they may not have a middle name
Many Thanks
You might be able to use the text to columns feature, with space as the delimiter. It should be under the data menu. I would try with one box first, and see the preview to see if it helps.The only issue I see is the no middle names. :: mike
First thing you will need to do is get rid of the Honorific Mr.
Simplest way is to do a Search/Replace.Then:
To get First Name:
=LEFT(A1,FIND(" ",A1,1))
To get Last Name, with NO Middle Name or Initial
=RIGHT(A1,LEN(A1)-FIND(" ",A1))
To get Last Name WITH Middle Name or Initial
=RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
To get Middle Name
=LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),1))
See how that works.
Check out this site for various options on separating names: http://www.cpearson.com/excel/first...
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
Hi Many Thanks for that I used the text to columns feature
Using the ISBLANK if there was nothing in the surname I replaced the previous name
Think that should work
Thanks
