How to Remove Middle Initial from Name in Excel


You can use the following formula in Excel to remove a middle initial from a name:

=TRIM(SUBSTITUTE(A2, TEXTBEFORE(TEXTAFTER(A2, " "), " "), ""))

This particular example removes the middle initial from the full name in cell A2.

For example, if cell A2 contains Andy R Miller then this formula would simply return Andy Miller.

The following example shows how to use this formula in practice.

Example: How to Remove Middle Initial from Name in Excel

Suppose we have the following list of names in Excel:

Now suppose we would like to remove the middle initial from each name.

We can type the following formula into cell B2 to do so:

=TRIM(SUBSTITUTE(A2, TEXTBEFORE(TEXTAFTER(A2, " "), " "), ""))

We can then click and drag this formula down to each remaining cell in column B:

Column B now contains the names in each corresponding cell in column A with the middle initial removed.

How This Formula Works

Recall the formula that we used to remove the middle initial from the name in cell A2:

=TRIM(SUBSTITUTE(A2, TEXTBEFORE(TEXTAFTER(A2, " "), " "), ""))

Here is how this formula works:

First, we use TEXTBEFORE(TEXTAFTER(A2, ” “), ” “) to extract the text between the spaces in the cell, which extracts just the middle initial.

For example, in the name Andy R Miller this extracts only R.

Then, we use the SUBSTITUTE function to substitute the middle initial with nothing.

Lastly, we use the TRIM function to remove any leading or trailing spaces.

The end result is that we’re able to remove just the middle initial from the name and return only Andy Miller.

The same process is used for each name in column A.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Extract Text Before a Character
Excel: How to Extract Text After a Character
Excel: How to Remove Specific Text from Cells

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *