Separating salutations from random data in MS-Excel

[Beethoven123]

I am trying to remove the salutations from an excel column. The column contains random data. Some names have prefix "Mr.", "Mrs." and some have nothing before the. For example:

• Ms. John Doe
• Mr. John Doe
• John Doe

What can I do in this case to remove the "Ms." and "Mrs." without affecting the prefix-less word?

Thanks in advance.

 

[Eric W]

Welcome to the Board.

A couple options:

Let's say your column is A, and the first entry is Ms. John Doe. Put "John Doe" in B1 (no quotes). Then with B1 selected, press Control+E. This activates Flash-Fill, and it will try to create entries that match the pattern of the example you just entered. If there are other changes you want, like removing Jr. or something from the end, clear column B, find an entry in A like that, enter the way you want it, then try Control+E again.

Or you could try this formula in B1:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Ms.",""),"Mr.",""),"Mrs.",""),"Dr.",""))

and drag down.

 

[jtakw]

Hi,

Here's another way:

Book1
	A	B	C
1	Ms. John Doe		John Doe
2	Mr. John Doe		John Doe
3	John Doe		John Doe
4	Miss Jane Doe		Jane Doe
5	Mrs. Doe		Doe
6	Dr. Eric W		Eric W
Sheet31

Cell Formulas
Range	Formula
C1	=TRIM(IFERROR(MID(A1,FIND(".",A1)+1,255),SUBSTITUTE(A1,"Miss","")))

You're on the list Eric

 

[JoeMo]

Here's a macro solution where you can add or remove salutations to fit your sheet. Change the column letter to match the column your names are in. Do this on a copy of your sheet to ensure its doing what you want because the changes cannot be reversed.

Sub ClearSalutations()
Dim Salutations
Salutations = Array("Mr. ", "Ms. ", "Dr. ", "Reverend ", "Professor ") ' add any pertinent salutations you wish here
Application.ScreenUpdating = False
With Columns("A")  'replace A with your column letter(s)
    For Each sal In Salutations
        .Replace sal, "", xlPart
    Next sal
End With
Application.ScreenUpdating = True
End Sub

 

[Beethoven123]

Thank you so much for your help guys. Your solutions worked like charms!! You've saved my day.

 

[jtakw]

You're welcome, welcome to the forum.