How to split names column to first and last name columns plus a notes column

**deleter** · 08-01-2017, 01:26 PM

Hi everyone, I have a column full of names but problem is some cells are blank or have more than one name plus text. Filling all the bank cells with a simple "N/A" was easy enough, however how can I split this up into first name, last name, and notes columns? Most cells have a name, followed by some text (e.g. John Smith (CEO)). Any suggestions are appreciated. Thank you!

names.PNG

**CK76** · 08-01-2017, 01:29 PM

Please upload sample workbook with expected output. Without it, we will be guessing at what you may want.

For an example. What should happen for John Doe, Jane Doe (MD)?

Should it extract just John as First Name and Doe as Last Name and (MD) as note?

**deleter** · 08-01-2017, 01:32 PM

Originally Posted by CK76

Should it extract just John as First Name and Doe as Last Name and (MD) as note?

Actually yes, that would be perfect. Any little note like that I can manually edit but just splitting everything up would be fantastic

**CK76** · 08-01-2017, 01:59 PM

Not the most efficient formula I'm sure. But should do what you are looking for as per your sample.

First Name:
=IFERROR(LEFT(K2,FIND(" ",K2)-1),"N/A")

Last Name:
=IF(ISNUMBER(FIND(",",K2)),MID(K2,FIND(" ",K2)+1,FIND("@",SUBSTITUTE(K2,",","@"))-FIND(" ",K2)-1),IF(ISNUMBER(FIND("(",K2)),MID(K2,FIND(" ",K2)+1,LEN(K2)-FIND("(",K2)-1),TRIM(RIGHT(SUBSTITUTE(K2," ",REPT(" ",50)),50))))

Contact Name Notes:
=IFERROR(RIGHT(K2,LEN(K2)-FIND("@",SUBSTITUTE(K2,"(","@"))+1),"")

See attached.