How to Abbreviate a Middle Name in a Full Name in Excel

Formatting names can be a manual and slow process. Here is a quick method to turn a middle name into a middle initial when given a full name.

Contents:

1. Formula

2. Explanation

2.1. Extract First Name

2.2. Extract & Abbreviate Middle Name

2.3. Extract Last Name

2.4. Combined Formula

Formula

To abbreviate a middle initial in a full name like "John Riley Smith" into "John R. Smith", use the following formula (assuming the name is in cell A1):

= TEXTBEFORE(A1, " ") & " " & LEFT(TEXTAFTER(A1," ")) & ". " & TEXTAFTER(A1," ", 2)

If you do not have access to the latest Excel formulas, this formula can be used across most Excel versions:

= LEFT(A1, FIND(" ", A1) - 1) & " " & LEFT(MID(A1, FIND(" ", A1) + 1, LEN(A1)), 1) & ". " & RIGHT(A1, LEN(A1) - FIND(" ", A1, FIND(" ", A1) + 1))

Explanation

In order to abbreviate the middle initial in a full name, we need to do three separate steps.

The first step is to return the first name, we can use TEXTBEFORE to return everything before the first space.

= TEXTBEFORE(A1, " ")

Next, we need to abbreviate the middle initial. We can do this by returning the middle name and then taking the first letter. We can combine LEFT and TEXTAFER to extract this.

= LEFT(TEXTAFTER(A1," "))

Lastly, we need to return the last name. We can use TEXTAFTER again, but this time, we need to use a "2" under the instance number argument to tell the function to pull everything after the second space (which should just be the last name).

=TEXTAFTER(A1," ", 2)

Combine everything together using "&" and the correct spaces and punctuation to get:

= TEXTBEFORE(A1, " ") & " " & LEFT(TEXTAFTER(A1," ")) & ". " & TEXTAFTER(A1," ", 2)