Parsing Names into Columns

[redhots4]

I have a list of names. Sometimes they are two names, sometimes 3-4 name, sometimes they include III as in John Smith III. Below I've shown some examples and expected outcomes. Can you help with a formula to accomplish this? (Not sure on the proper spelling of Jingleheimer, so I took my best guess.)

Full Name	Expected First Name	Expected Last Name
John Smith	John	Smith
John Jacob Smith	John	Smith
John J Smith	John	Smith
John Jacob Jingleheimer Smith	John	Smith
John Jacob Jingleheimer-Smith	John	Jingleheimer-Smith
John Smith III	John	Smith III

 

[RobP]

Hi, try this:

roster.xlsm
	A	B	C	D
1	John Smith	John	Smith
2	John J Smith	John	J Smith
3	John Jacob Smith	John	Jacob Smith
4	John Jacob Jingleheimer Smith	John	Jacob Jingleheimer Smith
5	John Smith III	John	Smith III
Sheet2

Cell Formulas
Range	Formula
B1:B5	B1	=LEFT(A1,FIND(" ",A1,1)-1)
C1:C5	C1	=MID(A1,FIND(" ",A1,1)+1,LEN(A1))

 

[redhots4]

Thank you! That works well for the First name, but doesn't return the proper result for last name. As an example, John Jacob Smith comes back as Jacob Smith (should just be Smith).

 

[RobP]

yes, sorry, my eyes went a little googly with the result you required with all those John Jacob Smiths...

So heres another try:

roster.xlsm
	A	B	C	D	E	F
1	John Smith	John	Smith		Smith
2	John J Smith	John	J Smith		Smith
3	John Jacob Smith	John	Jacob Smith		Smith
4	John Jacob Jingleheimer-Smith	John	Jacob Jingleheimer-Smith		Jingleheimer-Smith
5	John Smith V	John	Smith V		Smith
Sheet2

Cell Formulas
Range	Formula
B1:B5	B1	=LEFT(A1,FIND(" ",A1,1)-1)
C1:C5	C1	=MID(A1,FIND(" ",A1,1)+1,LEN(A1))
E1:E5	E1	=LET(arrx,(MID(A1,FIND(" ",A1,1)+1,LEN(A1))),arry,IFERROR((MID(arrx,FIND(" ",arrx,1)+1,LEN(arrx))),arrx),arrz,RIGHT(arrx,2),IF(OR(arrz="II",arrz=" I",arrz="IV",arrz=" V"),LEFT(arrx,FIND(" ",arrx,1)-1),arry))

The main issue is you have "Jacob Smith" where you want to ditch the [first name], and then you have "Smith III" where you effectively want to ditch the [second name].
So this will work to some extent, but specifically looks for those with "II or "III" on the end. It will work for names up to Smith "V" for example.

cheers
Rob