How can I extract the first initial and last name in Google Sheets?

By /

To extract the first initial and last name in Google Sheets, you can use the LEFT and RIGHT functions to split the name into two parts. First, use the LEFT function to extract the first letter of the first name by specifying the cell containing the full name and a number 1 as the number of characters to extract. Then, use the RIGHT function to extract the last name by specifying the cell containing the full name and the number of characters to extract, which can be calculated using the FIND and LEN functions. This will allow you to easily extract the first initial and last name from a full name in Google Sheets.

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

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:

x

Related terms:

  1. Count Specific Characters in Google Sheets How to count specific characters in google sheets?
  2. Google Sheets: Extract Text Between Two Characters
  3. Google Sheets: Extract Text Before a Character
  4. Google Sheets: Extract Text After a Character
  5. How do I extract the first word from a cell in Google Sheets?
  6. How do I use LEFT to extract text before space in Google Sheets?
  7. How to extract a year from a date in Google Sheets?
  8. How to Extract Last Word from a Cell in Google Sheets
  9. How to extract numbers from string in Google Sheets?
  10. Question: Google Sheets: Extract Top N Values from Range