Split data from name field
I need to extract the first name, middle initial and last name from a cell. I am able to extract all of them when there is a middle initial, however I get an error if the name has no middle initial. Attached is a spreadsheet with my formulas.
Thank you for any help you can give me.
FORMULAS
First Name Formula =LEFT(A7,FIND(" ",A7))
Middle Initial =IFERROR(MID(A9,FIND(".",A9)-1,2),MID(A9,FIND(" ",A9)+1,2))
Last Name Formula =IFERROR(RIGHT(A7,LEN(A7)-FIND(".",A7)-1),RIGHT(A7,LEN(A7)-FIND(" ",A7)-2))
RESULTS
Full Name First Name Middle Initial Last Name
Jeffrey R. Evers Jeffrey R. Evers
Robert R Gains Robert R Gains
Deborah Harris Deborah Ha rris
* Error - Works if middle initial has a period or if middle initial has no period, doesn't work if there is no middle initial
Last edited by dpic44; 02-05-2016 at 07:59 PM.
Reason: Added formulas
|