Google Sheets: How to Extract First Initial & Last Name


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

=LEFT(A2,1)&" "&REGEXEXTRACT(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 Bob Smith then this formula would return B Smith.

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

Example: Extract First Initial and Last Name in Google Sheets

Suppose we have the following list of full names in Google Sheets:

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)&" "&REGEXEXTRACT(A2,".* (.*)")

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

Google Sheets extract first initial and last name

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

How This Formula Works

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

=LEFT(A2,1)&" "&REGEXEXTRACT(A2,".* (.*)")

Here is how this formula works:

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

This extracted B.

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

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

This extracted Smith.

The end result is that the formula returned B Smith.

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

Additional Resources

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

Google Sheets: Check if Cell Contains Text from List
Google Sheets: Conditional Formatting if Another Cell Contains Text
Google Sheets: How to Calculate Average If Cell Contains Text

Leave a Reply

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