How to remove middle initials in Excel

In Excel, if you’re analyzing or processing a dataset and the middle name initials are not relevant to the task at hand, removing them can help streamline your analysis and make it easier to work with the data. When presenting data or creating reports, you may choose to omit middle name initials to maintain a cleaner and more concise format. This can be particularly useful when you have limited space or want to improve readability.

The dataset provides information on seven individuals along with their phone models, net worth, car models, and company names. The individuals’ names are listed in Column A, where each name includes a middle name initial. In Column B, simplified names without middle name initials will be written by following the methods presented in this tutorial.

Method 1 – By using Find and Replace tool

Step 1 – Select the Range of cells

• Select the Range of cells containing the names from which you want to remove the middle initials.
• For instance, we have selected the range (A2:A8).

Step 2 – Opening the Find and Replace Tool

• Go to the “Home” tab in the Excel ribbon.
• Click on the “Find & Select” button in the “Editing” group.
• Then select “Replace” from the drop-down menu.
• Alternatively, you can use the keyboard shortcut “Ctrl + H” to open the Find and Replace dialog box directly.
• After doing that, the Find and Replace tool will appear on your screen. 

Step 3 – Remove the Middle Name initials

• After the dialogue box has opened, enter space, an asterisk (*), and a space again in the “Find what” field. Just like this ( * ).
• Then, simply enter a space in the “Replace with” field.
• Now, click on Replace All option and all Middle Name initials will be removed from names in a selected range of cells. 
• Then close the Find and Replace tool.

Method 2 – Creating a User-Defined Function in VBA

Step 1 – Add a module

• Open your Excel workbook.
• Press “Alt + F11” to open the Visual Basic Editor.
• In the Project Explorer window, locate and select the workbook where you want to add the module.
• Right-click on the workbook name and select “Insert” from the context menu.
• Choose “Module” from the options. 
• This will insert a new module into the project.
• Double-click on the newly created module to open it in the code editor.

Step 2 – Writing the code

• Copy and paste the VBA code below into the module.

Function Rmv_mid_init(name As String) As String

    Dim nameArr() As String

    Dim i As Integer

    Dim result As String

    ‘ Split the name into an array of words using space as the delimiter

    nameArr = Split(name, ” “)

    ‘ Loop through each word in the array, excluding the first and last word

    For i = 1 To UBound(nameArr) – 1

        ‘ Check if the word is a middle initial (two characters with a period at the end)

        If Len(nameArr(i)) = 2 And Right(nameArr(i), 1) = “.” Then

            ‘ Replace the middle initial with an empty string

            nameArr(i) = “”

        End If

    Next i

    ‘ Combine the modified words into a single string without extra spaces

    result = Join(nameArr, ” “)

    ‘ Trim any leading or trailing spaces from the result

    result = Trim(result)

    ‘ Replace multiple consecutive spaces with a single space

    Do While InStr(result, ”  “) > 0

        result = Replace(result, ”  “, ” “)

    Loop

    ‘ Assign the final result to the function’s return value

    Rmv_mid_init = result

End Function

• Close the VBA Editor by using the shortcut key “Ctrl+Q”.

Step 3 – Select the cell

• Select an empty cell such as cell B2 in which you want to get the name without middle name initials.
• In this selected cell, we will apply the function created by us using VBA code to remove middle names and initials.

Step 4 – Using the created Function

• Press the = (equal sign) button on your keyboard in the selected cell. For example, cell B2.
• Then, type “Rmv_mid_init” (without quotes) and select this function from the list by using the tab button. 
• Now, select or enter the name of the cell from which we want to remove the middle name initial. For instance, it is the cell A2.
• Add closing parenthesis and press Enter.
• The result would appear in cell B2 which contains a name after removing the middle initials.

Step 5 – Applying the formula to the whole range

• For applying the formula on the whole range, select the cell in which the result is present. For example, it is a B2 cell in our case.
• Then move your cursor to the right bottom corner of the cell, and your cursor would turn into a + shape which is called the fill handle.
• Double-Click on this fill handle and the formula would be applied to the whole range.

Explanation of the Code:

The code used in this tutorial works following these steps;

• Take a full name as input
• The input name is split into an array of words using the Split function, with a space (” “) as the delimiter. Each word of the name is stored in the nameArr array.
• A loop is executed to iterate through each word in the nameArr array, excluding the first and last words. This loop checks if a word is a middle initial by verifying if it has a length of two characters and ends with a period.
• If a word is identified as a middle initial, it is replaced with an empty string in the nameArr array.
• After the loop completes, the modified words in the nameArr array are joined back together into a single string using the Join function, with a space (” “) as the delimiter. The modified name is stored in the result variable.
• Leading and trailing spaces in the result string are removed using the Trim function.
• To ensure there are no multiple consecutive spaces in the result string, a loop is executed. The loop checks if there are two consecutive spaces (” “) in the result string using the InStr function. If found, it replaces the double space with a single space using the Replace function. This loop continues until no more double spaces are present in the result string.
• Finally, the modified name stored in the result variable is assigned as the return value of the function Rmv_mid_init.