person name columns help!

Originally Posted by misc.v

This one shows, some other ones.
The only salutations that exist are MR, MRS, MISS, MS
Attachment 517686
MA, and MIMI are first names

OK Try

B2

Formula:

=IF(LEFT(A2,1)="M",LEFT(A2,FIND(" ",A2)-1),"")

C2

Formula:

=LEFT(TRIM(SUBSTITUTE(A2,B2,"")),FIND(" ",TRIM(SUBSTITUTE(A2,B2,"")))-1)

D2

Formula:

=IF(LEN(IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,""))))-LEN(SUBSTITUTE(IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,"")))," ",""))>0,LEFT(IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,""))),FIND(" ",IF(B2="",TRIM(SUBSTITUTE(A2,C2,"")),TRIM(SUBSTITUTE(A2,B2&" "&C2,""))))-1),"")

E2

Formula:

=TRIM(IF(AND(B2="",D2=""),SUBSTITUTE(A2,C2,""),IF(B2="",SUBSTITUTE(A2,C2&" "&D2,""),IF(D2="",SUBSTITUTE(A2,B2&" "&C2,""),SUBSTITUTE(A2,B2&" "&C2&" "&D2,"")))))