Name Extraction in Google Sheets

This tutorial shows you how to extract the first and last names from a name string, in Google Sheets.

Before we get started, you can view the actual file through the link below:

Name Extraction (Google Sheets)

The List of Names

Below is the list of names we plan to parse.

Extraction Steps

The format for the Name column is as follows: <First Name> <Space> <Last Name>

First Name Extraction Steps

  1. Find first occurrence/position of the space character.
  2. Return all characters from the beginning of Name, up to the position of the space character.
  3. Remove trailing spaces.

Last Name Extraction Steps

  1. Find first occurrence/position of the space character.
  2. From the position of the space character, get the remaining characters of the name string.
  3. Removing leading spaces.

First Name Extraction

Find First Occurrence Space Character

The FIND function will allow us to search for the space character in Name.

In cell B2, type =FIND(” “,$A2) and press Enter:

Copy formula down to remaining cells, which displays the positions for the first space of each name, as shown below.

Get All Characters before First Space

Adding the LEFT function will return all the characters from Name, up to the first space.

In cell B2, press F2 to go into edit mode or use the formula bar to modify the formula.

Type LEFT($A2, right after the equal sign; and, type ) to close out the formula at the end.  Now, the new formula is: =LEFT($A2,FIND(” “,$A2)).  See the image below.

Press Enter.

Copy the formula down.  Your table should show only the first names of the Name.

=LEFT($A2,FIND(” “,$A2)) is equivalent to =LEFT($A2,7)

Note: Although you cannot see it, the space character is included in the First Name column.

Last Name Extraction

Find First Occurrence/Position Space Character

Use the same procedure as you did for the first name extraction, to get the first occurrence of the space character, as shown below.

Get All Characters after First Space

To get all the characters after the space, we will use the MID function and the LEN function.

MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

LEN returns the number of characters in a text string.

In C2, type MID($A2, right after the equal sign.  And, type ,LEN($A2)) right after FIND(” “,$A2).  Your formula should look like the above image.

Press Enter.  Copy the formulas down.

Note: The LEN portion of the formula will return a number larger than what we need, but it does not matter because it will grab all the characters after the space.

Note: Now, you can see the space in front of the last name, which we need to fix.

Remove Leading and Trailing Space

There are a few ways to remove leading and trailing spaces from the first and last names.

First Method—Use the TRIM function

The TRIM function removes leading and trailing spaces from a string.  We can simply type TRIM in front of LEFT and in front of MID, like below.

First name: =TRIM(LEFT($A2,FIND(” “,$A2)))

Last name: =TRIM(MID($A2,FIND(” “,$A2),LEN($A2)))

Note: Do not forget the closing parenthesis at the end of the formula.

Second Method—Adjust the FIND Function

For the first name, you need to subtract one character representing the space, as shown below.

=LEFT($A2,FIND(” “,$A2)-1)

Note: Notice the -1 after the FIND function.

For the last name, you need to increase the position from the space by one, as shown below.

=MID($A2,FIND(” “,$A2)+1,LEN($A2))

Note: Notice +1 after the FIND function.

More Data Tutorials