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:

### 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)`