Splitting First and Last Names of a 3 Word Name

Try this (I gave you 1 for middle name too...

	A	B	C	D
2	aa bb cc	aa	bb	cc
3	aa 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

In B1

Formula:

Please Login or Register  to view this content.

In C1

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

Assuming you have b1 name

Then Try

C1

Formula:

Please Login or Register  to view this content.

and copy towards the cell for last name

Originally Posted by probuddha

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

This is impossible to do with 100% accuracy.

Consider these 2 names:

Eddie Van Halen
Betty Jo Spickerman

One is a 2 word last name and the other is a 2 word first name.

How would we tell Excel to differentiate one from the other?