How to remove Middle Name in Excel

Removing middle names in Excel can be a matter of personal preference or specific data formatting requirements. Middle names may not always be necessary or relevant for the particular data analysis or reporting you are working on. By removing middle names, you can simplify the data and focus on the essential information.

The dataset provided includes information about individuals, their purchased watches, and relevant details. The “Name” column contains the full names of the individuals, while the “Names Simplified” column represents the names with the middle names removed which we will learn in today’s tutorial how to remove. The “Watch Brand” column specifies the brand of the purchased watch. The “Watch Model” column provides the specific model or style of the watch. The “Date of Purchase” column indicates the date when each watch was bought. Lastly, the “Price ($)” column denotes the cost of each watch.

Method 1 – By using Flash Fill

Removing middle names by Flash Fill is the simplest method to remove middle names from a full name. If you want to keep it simple then this method is best for you.

Step 1 – Selecting the cell

• Select a vacant cell in which you want to get the name without a middle name.
• You must select the cell adjacent to the first full name cell. Otherwise, this method will not work.

Step 2 – Writing one name manually

• To make flash fill work, we need to enter one name without a middle name manually.
• For instance, we are typing “John Smith” manually in cell B2.

Step 3 – Using Flash Fill to remove middle names 

• After writing the name manually, press Enter and you will move to the cell below which is B3.
• Then press “Ctrl+E” to use Flash Fill.
• Now, every middle name will automatically be removed from all the names present in the Column A and the new simplified names will appear in Column B.

Method 2 – By using multiple Functions

Step 1 – Selecting the cell

• Select an empty cell in which you want to get the name without a middle name.
• In this selected cell, we will apply a combination of functions to create a formula to remove middle names.

Step 2 – Writing the Formula

• Write the following formula in your selected empty cell such as cell B2.

=IFERROR(TRIM(LEFT(A2, FIND(” “, A2)-1)&” “&MID(A2, FIND(“@”, SUBSTITUTE(A2,” “,“@”, LEN(A2)-LEN(SUBSTITUTE(A2,” “,“”))))+1, LEN(A2))),“”)

• After writing this formula, press Enter and the result would appear in the cell.

Step 3 – 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 the cell B2 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.

Method 3 – By using the function created VBA code

Step 1 – Adding a module

• For adding a module, navigate to the Developer tab.
• After that, click on the first option named Visual Basic.
• You’ll see a new window on your screen and you can also open this window by using the shortcut key (Alt+F11) as well.
• Then, click on the Insert tab in this window and click on the Module option.
• Now, a new module would open.

Step 2 – Writing the code

• Just copy and paste the following code in the white area of entered module.

Function RemoveMiddleName(ByVal fullName As String) As String     Dim nameParts As Variant     Dim firstName As String     Dim lastName As String         ‘ Split the full name into parts     nameParts = Split(fullName, ” “)         ‘ Get the first and last name     firstName = nameParts(0)     lastName = nameParts(UBound(nameParts))         ‘ Combine the first and last name     RemoveMiddleName = firstName & ” ” & lastName End Function Sub RemoveMiddleNames()     Dim ws As Worksheet     Dim dataRange As Range     Dim nameColumn As Range     Dim cell As Range         ‘ Set the worksheet containing the data     Set ws = ThisWorkbook.Sheets(“Sheet1”) ‘ Replace “Sheet1” with the actual sheet name         ‘ Define the range containing the data (assuming it starts from cell A2)     Set dataRange = ws.Range(“A2:E8”) ‘ Modify the range as per your dataset         ‘ Set the column containing the names     Set nameColumn = dataRange.Columns(1)         ‘ Loop through each cell in the name column     For Each cell In nameColumn         If Not IsEmpty(cell) Then             ‘ Remove middle name using the function             cell.Value = RemoveMiddleName(cell.Value)         End If     Next cell End Sub

• Then, use shortcut key “Ctrl+Q” to close the VBA Editor.

Step 3 – Selecting the cell

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

Step 4 – Using the created Function

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

Step 5 – Applying the formula to the whole range

• For applying the formula on the whole range, select the cell in which result is present. For example, it is B2 cell in our case.
• Then move your cursor to the right bottom corner of the cell, and your cursor would turn into + 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 formula used in Method 2:

We use the following formula;

=IFERROR(TRIM(LEFT(A2, FIND(” “, A2)-1)&” “&MID(A2, FIND(“@”, SUBSTITUTE(A2,” “,”@”, LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))))+1, LEN(A2))),””)

The explanation of this formula is as follows:

• SUBSTITUTE(A2, ” “, “@”, LEN(A2) – LEN(SUBSTITUTE(A2, ” “, “”)))

This function replaces all occurrences of spaces in cell A2 with “@” except for the last space. It effectively replaces the last space before the middle name with “@”.

• FIND(“@”, SUBSTITUTE(A2, ” “, “@”, LEN(A2) – LEN(SUBSTITUTE(A2, ” “, “”))))

This function finds the position of the “@” symbol in the modified string from step 1. It identifies the position of the last space before the middle name.

• MID(A2, FIND(“@”, SUBSTITUTE(A2, ” “, “@”, LEN(A2) – LEN(SUBSTITUTE(A2, ” “, “”)))) + 1, LEN(A2))

This function extracts the portion of the name starting from the position after the “@” symbol until the end of the name. It effectively removes the middle name from the name string.

• LEFT(A2, FIND(” “, A2) – 1)

This function extracts the portion of the name from the beginning until the position of the first space. It captures the first name.

• TRIM(LEFT(A2, FIND(” “, A2) – 1) & ” ” & MID(A2, FIND(“@”, SUBSTITUTE(A2, ” “, “@”, LEN(A2) – LEN(SUBSTITUTE(A2, ” “, “”)))) + 1, LEN(A2)))

This function combines the first name (extracted in step 4) with the portion of the name after removing the middle name (obtained in step 3). It trims any extra spaces and provides the final simplified name.

• IFERROR(<simplified name formula>, “”)

This function handles any errors that may occur in the simplified name formula. If an error occurs, it returns an empty string (“”).

Explanation of the VBA Code used:

The code has a function RemoveMiddleName which removes the middle names from a full name and a subroutine RemoveMiddleNames to apply this function to a range of names in a worksheet. 

The function RemoveMiddleName splits the full name into parts, extracts the first and last names, and combines them without the middle name. 

The subroutine RemoveMiddleNames iterates through each cell in a specified name column and applies the RemoveMiddleName function to remove middle names for each non-empty cell in the column.