Help with Breaking Down Names

[kpickens]

I have an exported file from a program that has the names of the parents of our students at school. One field has mothers name (ie: Smith, Jane) and one field has the fathers name (ie: Smith, Bob). I am needing to make two different fields....Short Salutation - Bob & Jane and a Long Salutation - Mr. & Mrs. Bob Smith. Can anyone help with formulas to do this? Many Thanks!

 

[guerillaunit]

For the short hand salutation:

=MID(B1, FIND(",", B1) + 2, 20) & " & " & MID(A1, FIND(",", A1) + 2, 20)

For the long hand salutation:

="Mr & Mrs. " & RIGHT(A1, LEN(A1) - (FIND(",", A1) + 1)) & " " & LEFT(A1, FIND(",", A1) -1)

These are assuming that the husband name is in cell A1 and the wife's name is in cell B1

 

[mgm05267]

Hi kpickens,

Welcome to the Board....

Assuming B2 has Mother's Name & C2 has Father's Name

For 1st one ...

D2 =MID(C2,FIND(", ",C2,1)+1,LEN(C2)) & " &" & MID(B2,FIND(", ",B2,1)+1,LEN(B2))

For 2nd one ...

E2 ="Mr. & Mrs." & MID(C2,FIND(", ",C2,1)+1,LEN(C2)) & " " & LEFT(B2,FIND(", ",B2,1))

Hope this is what you needed...

 

[kpickens]

Thanks to everyone for the help. This is wonderful and will save me an immense amount of time. Have a great weekend!