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.
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
Hi,
Thank you for pointing out that fallacy. To keep things simple, I would list the abbreviations by increasing text length.
Remove abbreviations appearing before a name
{ 2 Comments }