splitting first, last, middle initial in separate columns

**johnmerlino** · 10-11-2010, 09:51 PM

Hey all,

I am having some difficulty coming up with an excel formula that splits last name, first name, and middle initials in separate columns. There's variation where the middle initial may be one letter or an entire word and sometimes the character "&" is tossed at the end of column:
CAGLE,DANNY D &
SNEIR,LARRY &
ALONSO,MERRIL M
KIM,JIN SOO &
HARRISON,GEORGE & NANCY
LUKS,BARBARA VACCARO
MILES,JAMIE & MARLON D
LITT,JEFFREY D & LISA J

Is there an excel calculation to get last, first, and middle initial (whether one character or several) in their own columns while getting rid of the "&" unless it's between two first names? So basically the "&" goes away if it's the very last character. Note that if there's two first names, then the "&" would stay and only the initial of righter most name would be split in own column.

Thanks for any response

**teylyn** · 10-11-2010, 11:07 PM

Hi,

do a text to columns with a comma as the separator. That will leave you with two columns, the first one has the last name only.

Next, do a text to columns on the second column, this time using the & as the delimiter.

Now you'll have three columns, i.e. Last name, first name, spouse name

**DonkeyOte** · 10-12-2010, 04:39 AM

If we assume strings are in A1:An then based on the samples:

B1:
=LEFT(A1,FIND(",",A1)-1)
copied down

C1:
=REPLACE(LEFT(A1,FIND(" ",A1)-1),1,LEN(B1)+1,"")&IF(OR(RIGHT(A1)="&",ISERR(FIND("&",A1))),""," & "&TRIM(LEFT(SUBSTITUTE(REPLACE(A1,1,FIND("&",A1)+1,"")," ",REPT(" ",LEN(A1))),LEN(A1))))
copied down

D1:
=TRIM(IF(AND(ISNUMBER(FIND("&",A1)),RIGHT(A1)<>"&"),IF(LEN(REPLACE(A1,1,FIND("& ",A1&"& ")+1,""))-LEN(SUBSTITUTE(REPLACE(A1,1,FIND("& ",A1&"& ")+1,"")," ","")),RIGHT(SUBSTITUTE(REPLACE(A1,1,FIND("&",A1)+1,"")," ",REPT(" ",LEN(A1))),LEN(A1)),""),SUBSTITUTE(LEFT(A1,FIND("&",A1&"&")-1),LEFT(A1,FIND(" ",A1)-1),"")))
copied down

The above would return:

CAGLE | DANNY | D
SNEIR | LARRY
ALONSO | MERRIL | M
KIM | JIN | SOO
HARRISON | GEORGE & NANCY
LUKS | BARBARA | VACCARO
MILES | JAMIE & MARLON | D
LITT | JEFFREY & LISA | J

note per your requirements where multiple names and multiple middle name/initials only the right most is used

Goes without saying that with more thought the above can be shortened significantly ... that said a UDF might still be preferable.