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.
Step 2: Press Ctrl+H on your keyboard to open the Find and Replace dialog.
Step 3: Inside the text box next to “Find what:”, type a space, an asterisk (star, *), and then 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:”.
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:
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:
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.
Step 2: Click on the cell that contains the full name without the 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.
Flash Fill will automatically detect the pattern without the middle name, and apply it to other cells:
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.
Step 3: Click on 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.
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:
Step 6: Click on Finish.
This way, we have separated the first, middle, and last names using the Text to Columns wizard.
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):
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:
This way, we have removed the middle name from all the full names in our spreadsheet:
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.