Separate out first and last name

[Nordicrx8]

Hey all!

I have a very long list of employees in which I need to extract their names into a consistent format.

I'm using the below formulas with a 99% success rate, however employees with a suffix of "JR" or "II" (etc) are throwing a monkey wrench in the calculation.

First:
=MID(P14082,FIND(" ",P14082,1)+1,256)

Last:
=LEFT(P14082,(FIND(" ",P14082,1)-1))


Here is the format of my data:

Firstname Suffix LastName MiddleInitial


I ONLY need the first and last name. Is there a formula out there that can overcome the 2% of my names that have a suffix?

Thanks for the help team!

Best,
Chris

 

[kweaver]

Hamedo89: in your last post, you pull the middle name (or initial), not the last name, if there isn't a Jr., etc.

 

[kweaver]

You can also get the last name from your structure using this (ugly) formula:

=LEFT(IF((LEN(A1)-LEN(SUBSTITUTE(A1," ","")))<2,A1,RIGHT(A1,LEN(A1)-FIND("/",SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))))),FIND(" ",IF((LEN(A1)-LEN(SUBSTITUTE(A1," ","")))<2,A1,RIGHT(A1,LEN(A1)-FIND("/",SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))))))-1)

 

[Nordicrx8]

This worked - thanks so much!

Sorry for the late reply - work has been super busy and I haven't had a chance to work on this project.

thank you ALL for your time, responses, and help!

Happy Monday!

I didn't account for the suffix and middle name initial in my last name formula, revised version below.

=LEFT(MID(MID(A1,FIND(" ",A1)+1,256),IFERROR(FIND(" ",MID(A1,FIND(" ",A1)+1,256)),0)+1,256),IFERROR(FIND(" ",MID(MID(A1,FIND(" ",A1)+1,256),FIND(" ",MID(A1,FIND(" ",A1)+1,256))+1,256)),255)-1)