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

 

[skyhigh_ct]

Perhaps a follw-up formula in the next cell that removes any alpha if present?
S DINHOFER MD returns DINHOFER MD
Thank you

 

[AliGW]

OK - that's me out of ideas, sorry. You've now added criteria for which I cannot find any logic that Excel will understand without having some definitive list to refer to. I think you are going to need to state exactly what is and what is not allowed as a suffix, and give every possible combination of name formats, otherwise we'll just be going round in circles all afternoon!

 

[skyhigh_ct]

Allowed suffixes are MD, DO or nothing.
Would it be easier to eliminate suffixes? I can live without the suffixes if that would make this work.
Thank you very much.

Thank you

 

[AliGW]

That's helpful. I'm away from my laptop at present, so maybe someone else will take this up. Are you sure there are no further criteria that need accommodating?

 

[skyhigh_ct]

This should do it. Thank you

John Doe MD > Doe
John H More DO > More
John Deere > Deere

 

[Peter_SSs]

IF you names can be all capitalised like your samples in post #7 then I'm with Joe. I don't think this is possible.
Certainly you would need to have a complete list of possible suffixes to even get close.
However, even then it would not be possible to be sure. Another example. Suppose "PO" is an allowable suffix and a cell contains "PAUL KEENE PO"
That could mean "PAUL KEENE" (no MI) with suffix "PO"
or it could mean "PAUL PO" whose middle name is "KEENE".

However, IF all the names are standard Upper/Lower case and the suffix is all upper case, then the problem is solvable as the two examples I gave above would be different & therefore distinguishable.
The first would be: "Paul Keene PO"
The second would be: "Paul Keene Po"

 

[skyhigh_ct]

My apologies, I should be more clear.
All data will be capitalized.
The only possible suffixes are MD DO (no suffix)

Thank you

 

[Peter_SSs]

The only possible suffixes are MD DO (no suffix)

So no "RN" suffixes like some of your earlier examples? Then assuming "DO" will never be a LN (pretty resonable assumption ), then would this suffice, depending on whether you want LN + SUFFIX or just LN

Excel 2010 32 bit
	A	B	C
1	ANTHONY BURGOS MD	BURGOS MD	BURGOS
2	BENJAMIN W STRONG MD	STRONG MD	STRONG
3	DAVINDRA SEELAGAN MD	SEELAGAN MD	SEELAGAN
4	DEREK J PLAKYDA MD	PLAKYDA MD	PLAKYDA
5	EDWARD CALLAWAY MD	CALLAWAY MD	CALLAWAY
6	JOHN H MORE DO	MORE DO	MORE
7	JOHN H MORE	MORE	MORE
8	DEREK J PLAKYDA	PLAKYDA	PLAKYDA
9	KEN JONES	JONES	JONES
Names

Cell Formulas
Range	Formula
B1	=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),50+50*OR(RIGHT(A1,3)={" MD"," DO"})))
C1	=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-3*OR(RIGHT(A1,3)={" MD"," DO"}))," ",REPT(" ",500)),50))

You would still have the problem mentioned by Joe though:

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)

 

[skyhigh_ct]

Perfect.
Thank you Peter, AliGW, & Joe.

This will meet 99% of the scenarios. two-part last names or other suffixes will/can be manually corrected.

All the best, Jason

 

[AliGW]

You're welcome!