How to Remove Middle Name from Full Name in Excel

Do you know how to remove the middle name from the full name? In this blog, we would learn different ways that can be used to remove the middle name from the full name (FirstName_MiddleName_Surname).

Below is the example data table where we have full names in column A. Now, we want to remove the middle name and only keep first and last name as shown in the screenshot below:

There are three ways using which you can achieve the same:

1. “Find and Replace” functionality
2. Formula based approach
3. “Text to Column” functionality

Let us try to understand each of the above methods.

Remove Middle Name Using “Find and Replace”

This is not a  formula-based approach. It is a widely used method to remove the middle name from the Full name because of ease of its use and it is a quicker approach than any other approach.

Follow the below steps :

Firstly, select the cells that contain the list of full names.

Now, open the “Find and Replace” dialog box by pressing Ctrl+H (Control key followed by H) on your keyboard.

In the “Find What” section, press space on your keyboard, then insert the asterisk symbol (*) and again press space on your keyboard.

<Space><Asterisk><Space>

In the “Replace With” section just press space on your keyboard. 

Now Click on the “Replace All” button.

Finally, you would notice that the middle name got removed from the full name in the selected cells.

Remove Middle Name Using Formula based approach

The method explained above was a non-formula based approach. However, you can also remove the middle name from the full name using formulas. 

The formula(s) that would be used to remove the middle name from the full name is the combination of many formulas.

Enter the following formulas in cell B2, C2 and D2:

In cell B2 : =LEFT(A2,FIND(” “,A2)) 

[This would return the first name]

Now press the “Enter” key on your keyboard to get the result.

Copy this formula and paste it to the other cells in column B to get the first name from the list of complete names.

In cell C2 : =RIGHT(A2,LEN(A2)-FIND(” “,A2,FIND(” “,A2)+1))

[This would return the last name]

Copy the formula in cell C2 and paste it in the other cells in column C.

Now to get the first and last name in one cell, enter the following formula in cell D2:

=CONCATENATE(B2,C2)

You would notice that the middle name is now available in cell D2.

Copy this formula to other cells in column D.

Using “Text to Column” Functionality

“Text to Column” functionality is a very amazing and helpful tool provided by excel to delimit the text into different columns. We would use this functionality to remove the middle name from the full name in excel.

Follow the below steps to achieve the same :

Select the cells that contain the full name.

In the Excel Ribbon Bar, click on the tab “Data”. Click on the “Text to Column” button. You can find this button under “Data Tools” group.

The “Text to Column” dialog box would open as shown in the screenshot below:

In the first step of this dialog box, select the option “Delimited” in the “Original Data type” section and click on the “Next” button.

In the second step of this dialog box, under the “Delimiters” section, deselect all the checkboxes and select the checkbox “Space” as shown in the screenshot below:

Under the “Data Preview” section, you would notice that the full names would be separated with horizontal lines.

Click on the “Next” button.

You have now reached the last step of the “Text to Column” dialog box. In this step, you need to select the destination cell. 

In the “Destination” section box, enter =$B$2 and press the “Finish” button.

You would notice the first name middle name and the last name got separated into different cells as shown in the screenshot below:

Now to group the first name and the last name, enter the following formula in cell E2.

=CONCATENATE(B2,” “,D2)

You would notice that the first name and last name (without middle name) in cell E2.

Copy this formula to other cells in column E to get the names (without middle name).

I hope you have enjoyed this tutorial. Share your views and comments in the comment section below.