Excel Split Full Name

[sqldba20]

I have data-set with full name. I want to split the first and Middle name in one column and the Last name in separate column. Some of the names have middle names between first and last name whereas as some do not. Also, some have last name with I and II (record 6 and 7). I tried =RIGHT(A12,LEN(A12)-RIGHT(FIND(" ",A12,1))) but it is putting the Middle Name and Last Name together.

Thanks You!

 

[hagia_sofia]

Maybe there are more possible combinations but try this as a start:

=LET(
spaces,LEN(A12)-LEN(SUBSTITUTE(A12," ","")),
IFS(spaces=1,HSTACK(TEXTBEFORE(A12," ",1),TEXTAFTER(A12," ",1)),spaces>1,HSTACK(TEXTBEFORE(A12," ",2),TEXTAFTER(A12," ",2))))

 

[Cubist]

One option.

Book1
	A	B	C
1	Full Name	First Name	Last name
2	James Lane	James	Lane
3	Mel Troy Gibson	Mel Troy	Gibson
4	Smith Aaron	Smith	Aaron
5	Paul Sam James	Paul Sam	James
6	Ian A. Bishop I	Ian A.	Bishop I
7	Ian A. Bishop II	Ian A.	Bishop II
8	Joseph T. Brown	Joseph T.	Brown
Sheet2

Cell Formulas
Range	Formula
B2:B8	B2	=LET(x,ISNUMBER(FIND(".",A2)),TEXTBEFORE(A2,IF(x,"."," "),-1) & IF(x,".",""))
C2:C8	C2	=TEXTAFTER(A2,IF(ISNUMBER(FIND(".",A2)),". "," "),-1)

 

[alansidman]

Investigate Flash Fill which does not require any formula. It is on the Data Tab --Data Tools