This post will guide you how to remove salutations from names in the range of cells in excel. How do I remove the salutation from the names cells with formula in Excel 2013/2016/2019/365. For example, if you have name salutations and you want to remove those salutations from name strings in excel, how to achieve it. How to split salutation and only names string into separate columns with excel formula.

1. Removing Salutation from Name

To remove salutation from names string in excel, you can create a formula based on the RIGHT function, the LEN function and the FIND function.

Assuming that you have a name list in the range of cell B1:B4, and you want to remove all salutations from those cells, you can write down this formula to achieve the result.

=RIGTH(B1,LEN(B1)-FIND("",B1))

You just need to type this formula into a blank cell, then press enter key in your keyboard. Then you still need to drag the AutoFill Handler over other cells to apply this formula to remove salutations.

Let’s see the last result:

2. Removing Salutation from Name with VBA Code

You can also remove salutations from names in a given range of cells using a VBA macro in Excel, and using Application.InputBox function to select cells.

Step1: press Alt + F11 shortcut to open the Visual Basic Editor.

Step2:  click Insert menu and choose Module to insert a new module.

Step3: write the following VBA macro code in the newly inserted module.

Sub RemoveSalutation()
    Dim rngSource As Range
    Dim rngDestination As Range
    Dim cell As Range
    
    ' Prompt for selecting the range of cells containing the names
    On Error Resume Next
    Set rngSource = Application.InputBox("Select the range of cells containing the names:", Type:=8)
    On Error GoTo 0
     
    ' Loop through each cell in the selected range
    For Each cell In rngSource
        ' Remove the salutation from each name using the Replace function
        cell.Value = Replace(cell.Value, "Mr.", "")
        cell.Value = Replace(cell.Value, "Mrs.", "")
        cell.Value = Replace(cell.Value, "Miss.", "")
        cell.Value = Replace(cell.Value, "Dr.", "")
        ' Add more salutations as needed
    Next cell
End Sub

Step4: you need to press key F5 or go to the Run menu and choose Run Sub/UserForm to execute the VBA macro.

Step5: a dialog box will appear, prompting you to select the range of cells that contain the names, and click on ok button.

Step6: the salutations would be removed from each name in the selected range.

You have removed salutations from names successfully in Excel.

3. Split salutations and name strings into different columns

If you want to split salutations and name string into different columns, you can extract the salutation string firstly, then extract the rest name string again. You need to create the first formula based on the LEFT function and FIND function to extract salutation string. And you still need to use another formula based on the MID function and the FIND function to get the names strings from those cells. Like these two formulas:

=LEFT(B1,FIND(" ",B1)-1)

=MID(B1,FIND(" ",B1)+1,100)

Type the first formula into a blank cell to get the salutation strings, then press enter key and drag the AutoFill Handler over other cells.

Type the second formula into a blank cell in a new column to get the name strings, then press enter key and then drag the AutoFill Handler over other cells to apply this formula to extract names.

4. Video: Removing Salutation from Name in Excel

This video will show you four operations on a name list consisting salutation and names in Excel 2013/2016/2019/365.

5. Related Functions

• Excel LEFT function
  The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
• Excel FIND function
  The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
• Excel RIGHT function
  The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[num_chars])…
• Excel MID function
  The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
• Excel LEN function
  The Excel LEN function returns the length of a text string (the number of characters in a text string).The syntax of the LEN function is as below:= LEN(text)…