Remove abbreviations appearing before a name

{ 2 Comments }

Assume there is a list of names appearing in range A2:A9.  Before these names, there are abbreviations such as Dr., Mr. Mrs. etc.  To worsen the situation, there may/may not be a space after the abbreviation.  A sample dataset may look like this:

Mr.Ramakrishna
Mr Ramakrishna
Mr. Ramakrishna
MrRamakrishna
PhD.Saurav
Dr (Mrs.) Indu Sharma
Dr. (Mr) Rakesh Singh
Mr. Mrinal Joshi

The task is to extract only the names in another column.

You may refer to my solution in this workbook.

Leave a Comment

Your email address will not be published.

*

  • Hi Ashish

    The formula you propose for this seems to be dependent on the order in which you type your list of standard abbreviations. If you were to change the order of G2 and G5, for example (have “Mr.” as your first entry, and then “Mr” as your fourth entry) the formula stops working effectively.

    How do you propose addressing this? Order your list of abbreviations by increasing text length?

    Thanks

    Patrick