splitting first, last, middle initial in separate columns

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.