Excel: How to Extract First Initial and Last Name

You can use the following formula in Excel to extract the first initial and last name from a full name in a cell:

=LEFT(A2,1)&" "&(TEXTAFTER(A2," "))

This particular example extracts the first initial and last name from the full name in cell A2.

For example, if cell A2 contained the full name John Smith then this formula would return J Smith.

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

Example: Extract First Initial and Last Name in Excel

Suppose we have the following list of full names in Excel:

Suppose we would like to extract the first initial and last name from each full name.

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

=LEFT(A2,1)&" "&(TEXTAFTER(A2," "))

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

Excel extract first initial and last name

Column B now contains the first initial and last name of each full name in column A.

Recall the formula that we used to extract the first initial and last name from the full name (John Smith) in cell A2:

=LEFT(A2,1)&" "&(TEXTAFTER(A2," "))

Here is how this formula works:

First, we used LEFT(A2,1) to extract the first character from the cell.

This extracted J.

Then we used &” “& to concatenate a single space.

Then we used TEXTAFTER(A2, ” “) to extract all text after the space in the cell.

This extracted Smith.

The end result is that the formula returned J Smith.

The same logic was used to extract the first initial and last name from each full name in column A.

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