Hi
I have a spreadsheet containing a column (B) of full names, which consists of 2,3 or even 4 word names.
Kindly advise how I can split them up in 2 columns as First and Last Name
Many Thanks
Hi
I have a spreadsheet containing a column (B) of full names, which consists of 2,3 or even 4 word names.
Kindly advise how I can split them up in 2 columns as First and Last Name
Many Thanks
Try this (I gave you 1 for middle name too...
A B C D 2aa bb cc aa bb cc 3aa bb cc aa bb cc
Option 1...
B2=LEFT(A2,SEARCH(" ",A2,1)-1)
C2=MID(A2,LEN(B2)+2,LEN(A2)-LEN(B2)-LEN(D2)-2)
D2=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
OR option 2...
B3=TRIM(MID(SUBSTITUTE($A3," ",REPT(" ",LEN($A3))),LEN($A3)*(COLUMNS($A$3:A3)-1)+1,LEN($A3)))
copied across
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
Removed by JT
In B1In C1Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
A B C 1 1st Mid1 Mid2 Lst 1st Lst 2 1st Lst 1st Lst 3 1st Mid1 Lst 1st Lst
Dave
Assuming you have b1 name
Then Try
C1and copy towards the cell for last nameFormula:Please Login or Register to view this content.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks