Parse Last Name, exclude MI, keep suffix

[skyhigh_ct]

Hello. I have researched & read numerous posts.
Still searching for formula to parse last name, exclude middle initial & keep any suffixes.

John Doe MD > Doe MD
Jane Doe RN > Doe RN
John H Doe DO > Doe DO
John Doe > Doe
John H Doe Jr MD > Doe Jr MD

Thank you

 

[AliGW]

How do you envisage that Excel will distinguish between a text string that is a first name and one that is a second name? Is the first name always the first in the string? In other words, do you have any in the format Doe DO John?

 

[skyhigh_ct]

For this purpose, the data will always be in the same format. FN MI (if applicable) LN suffix
Thank you.

 

[deciog]

skyhigh_ct

My contribution


=MID(D5,FIND("Doe",D5,1),50)


Decio

 

[skyhigh_ct]

I guess I should have been more specific. The last names will not always be Doe.

John Doe MD > Doe MD
Jane may RN > May RN
John H More DO > More DO
John Deere > Deere
John H Done Jr MD > Done Jr MD

 

[AliGW]

This works on your sample data:

=IF(H1<3,RIGHT(F1,LEN(F1)-FIND(" ",F1)),RIGHT(F1,LEN(F1)-FIND(" ",F1,FIND(" ",F1)+1)))

 

[skyhigh_ct]

So close. Thank you AliGW.

For names without middle initials, it is returning just the suffix.
ANTHONY BURGOS MD > MD (look to return BURGOS MD)
BENJAMIN W STRONG MD > STRONG MD
DAVINDRA SEELAGAN MD > MD (look to return SEELAGAN MD)
DEREK J PLAKYDA MD > PLAKYDA MD
EDWARD CALLAWAY MD > MD (look to return CALLAWAY MD)

Thank you

 

[AliGW]

Sorry - incomplete formula given! Should be this:

=IF((LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)<3,RIGHT(A1,LEN(A1)-FIND(" ",A1)),RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1)))

 

[Joe4]

I have seen this many times before. Note that it is very unlikely that you can come up with a formula to handle all cases correctly.

For example, what if you had these two names:
Mary Jo Smith
Lucy Van Pelt

Both have three words, but in the first one, the first name is two words (Mary Jo) and the last name is one word (Smith)
In the second one, the first name is one word (Lucy) and the last name is two words (Van Pelt)

There is no way Excel is going to be able to determine exactly where the split should occur in examples like this, since there is no "hard-and-fast" rule you can come up with to program to.

The best you can usually do is to program to handle the most common situations, but it is still going to require someone to review them afterwards to look for exceptions that do not follow the rules you have programmed.

 

[skyhigh_ct]

Almost.
Names with MI are retuning with MIs. I am looking to not show MIs. Thank you again. I really appreciate it.

Original Data Returned
DAVID S DINHOFER MD > S DINHOFER MD (looking to not show MI)
DAVINDRA SEELAGAN MD > SEELAGAN MD (perfect)
DEREK J PLAKYDA MD > J PLAKYDA MD (looking to not show MI)
DONALD S OSTRUM MD > S OSTRUM MD (looking to not show MI)
EDWARD CALLAWAY MD > CALLAWAY MD (perfect)