Reformatting Titles

This doesn’t directly answer your question, but it’s the best way to keep things flexible without tearing your hair out. Any way you could have “First Name” and “Last Name” as two separate fields, potentially with a third “Degree” field to cover the MD? If so, then you could easily create two formula fields for both:

• {First Name} & ‘ ‘ & {Last Name} & ‘, ‘ & {Degree} to build “John Smith, MD”
• ‘Dr. ‘ & {Last Name} to build “Dr. Smith”

The trouble with your initial idea is that not only is the comma inconsistent, but there’s also no way of knowing if a person has a non-hyphenated, two-word first or last name, which means you can’t assume that the last word is the last name.

If you’re trying to create this first/last name split from an existing list of names, this is a little more feasible with automation scripting, or with Javascript in general, but it’s something you’d need to run or schedule separately, not something that would auto-update like a formula field.

Structurally thelus is right that it’s easier to store your data that way, I presume you’re already working with a pre-existing list. Here’s a formula that'll mostly get you there…

'Dr.' & REGEX_EXTRACT({Name}, "(\\s+[^\\s]+)")

Replace {Name} with your name field. This basically takes the second word in the string and appends Dr. on the front. As mentioned by thelus there can be some problems, specifically with two word first names ( Anna Beth Scott MD would show as Dr. Beth), middle names or middle initials.  

Thank you both! ​@DisraeliGears01 as you suspected, I am working from a pre-existing list that is being fed over from another system. I will give that a try and edit individually as needed.