How to Remove Middle Initial from Full Name in Excel

A spreadsheet may contain useful personal information, such as the full name, age, occupation, etc. Sometimes, you may need to remove the middle name or middle initial from a full name. Fortunately, there are some very useful features and tools in Excel that make this possible.

In this tutorial, we will learn how to remove a middle name or initial using Find and Replace, Flash Fill, and Text to Columns.

Let’s dive into it.

Using Find and Replace to Remove Middle Initial

Find and Replace is a very handy tool, where a text can be found and replaced with another throughout the whole spreadsheet. If you want to use this tool to remove all middle names or initials, follow these steps.

Step 1: Select the cell/cells that contain a full name.

Select cells with full names

Step 2: Press Ctrl+H on your keyboard to open the Find and Replace dialog.

Find and replace dialog box

Step 3: Inside the text box next to “Find what:”, type a space, an asterisk (star, *), and then another space.

Type a space an asterisk and another space

This is a wildcard that finds all words that are surrounded by two spaces, which corresponds to all middle names.

Step 4: Write a single space inside the text box next to “Replace with:”.

Type a space next to replace with

Step 5: Click on Replace All.

A pop-up will appear, showing how many replacements have been made, and all the middle names have been removed from the full names:

Middle names removed with find and replace

If you want to preserve the original full name, you can copy and paste the full names in a new column, and perform Steps 1-5 on the newly copied cells.

This method also works for removing middle initials:

Middle initials removed with find and replace

Using Flash Fill to Remove Middle Initial

Flash Fill is an extremely handy feature that detects and replicates patterns. To use this feature for removing middle names or initials, you just need to type a full name without the middle name once, and then the pattern can be replicated for the rest of the data.

Step 1: For the first full name in your dataset, write the full name without the middle name in the cell right next to it.

Write a full name without middle name

Step 2: Click on the cell that contains the full name without the middle name.

Click on the cell without middle name

Step 3: Click on the Data tab, and then the Flash Fill icon on the upper right of Text to Columns, under Data Tools.

Click on the flash fill icon

Flash Fill will automatically detect the pattern without the middle name, and apply it to other cells:

Middle names removed with flash fill

This method works for proper data, but if you encounter problems with larger data that contain other patterns, you may use other methods to remove middle names. Just make sure that you check the output of Flash Fill, in order not to have erroneous results.

Remember also that Flash Fill returns a static result. Therefore, any change made to the full names will not automatically be reflected in the full names without the middle names. You may need to perform the same operations after an alteration to the data has been made.

You can use the Flash Fill feature to remove middle initials too.

Using Text to Columns to Remove Middle Initial

It is also possible to first split the full name into three parts (the first name, the middle name, and the last name), and afterward, combine the first and last name together. Let’s see how we can achieve that.

We can easily separate the full name using the Text to Columns tool. This will split any text into substrings, separating the text with a delimiter (separator). After splitting the full name, we will put together the first and last name, thus getting rid of the middle name.

Step 1: Select the cells that contain a full name.

Step 2: Open the Data tab, and then click on Text to Columns.

Click on text to columns

Step 3: Click on Next in the Text to Columns Wizard.

Click next in the Text to columns wizard

Step 4: Only check the Space delimiter, and uncheck all other delimiters. Make sure that the box next to “Treat consecutive delimiters as one” is unchecked, and then click on Next.

Select delimiter as space

Step 5: Select where you want to insert the separated data. Click on the Destination text box, and select the cell.

We can insert the separated names starting from B2 in this example:

Select the inserting destination

Step 6: Click on Finish.

This way, we have separated the first, middle, and last names using the Text to Columns wizard.

Full name separated with text to columns

Now that we have the first and last names separately, we can combine them with the ampersand (&) operator.

Step 7: In a new cell, write the following formula:

=B2 & " " & D2

The ampersand operator concatenates the first name (B2), a space (“ “), and the last name (D2):

First and last name are concatenated with ampersand

Step 8: To apply this formula to other cells, click on the small rectangle on the bottom right of the cell that contains the formula, and drag down:

Drag the formula down to apply to all cells

This way, we have removed the middle name from all the full names in our spreadsheet:

Middle name removed with Text to columns

The three methods that we have practiced in this tutorial are extremely easy, and they all work for both removing middle names, and middle initials. Now it’s up to you to choose which method suits you best.

Leave a Comment