How to separate first & last names with Google Sheet

Below is a simple formula to separate a name into a First Name column and a Last Name column in Google Sheets. Where A1 is the cell containing the name you wish to separate.

=SPLIT(A1," ")

Note the subsequent columns will need to be empty or you will see the following error. By simply removing the data in C1 and your SPLIT function will work as expected.

#REF!

Formula Explained

Function Used

• SPLIT

The SPLIT function will split the contents of the referenced cell by the specified delimiter. In the formula above we are splitting the contents of A1 by every space present in A1. This works great for cells containing first and last names or even first, middle, and last names. SPLIT is a great solution as long as all the content in column A is consistent. However, if the column you are attempting to separate has varying data another formula will need to be used.

Expanding Our Name Splitting Formula

Let’s say you have a column of names, but some cells have a middle name or initial while others just have a first name and some cells have a first and last name. Let’s create some additional formulas to address this situation.

Formula Goals

Practice Sheet

Make a copy of our practice sheet to follow along as we work on adding additional formulas. In our practice sheet we are assuming that the first word in the cell corresponds to a person’s first name and the last word in the cell corresponds to the person’s last name.

Separate First Name

Let’s start with separating the first name or word in a cell into another column. We will accomplish this by referencing all characters after the first space in cell A1, which contains a person’s full name.

=IFERROR(IF(ISBLANK(A1),, LEFT(A1,FIND(" ",A1)-1)),A1)

Functions Used

• FIND
• LEFT
• ISBLANK
• IF
• IFERROR

Formula Explained

Our formula’s first goal is to find the first space in cell A1. To accomplish this we will use the FIND function to return the position in cell A1 of the first space. The FIND function will return a numerical value.

We can then subtract 1 from the value our FIND function produced, to specify the number of characters we would like our LEFT function to extract. Below is our formula to pull the first name or word from cell A1.

=LEFT(A1,FIND(" ",A1)-1)

Let’s expand our formula to account for cells that only have a first name or that are blank to prevent our formula from displaying an error.

Ignoring Blank Cells

We can expand our previous formula to ignore cells in column A that are blank with a few functions.

Additional Functions

• ISBLANK
• IF

The addition to our previous formula now features IF and ISBLANK functions. This formula will allow you to use the autofill function to only display a first name value for rows with text in column A.

=IF(ISBLANK(A2),, LEFT(A2,FIND(" ",A2)-1))

1. ISBLANK returns a boolean value of either TRUE or FALSE depending if the referenced cell is empty.
2. With the IF function we can now create two options for our formula.
   1. If ISBLANK(A1) is blank, then a value of TRUE is returned. This would result in the IF function returning the value_if_true value. This is the value after the first comma. Which we have specified to be blank. If there is no name in column A we would like our first name column to leave the formula cell blank.
   2. If ISBLANK(A1) is not blank, then a value of FALSE is returned. This would mean there is content within the name column, then the IF function returns the value_if_false value. In this case, this would run our previous formula to find the numerical position of the first space, subtract 1, and return all characters to the left of the first space.

Only First Name Data

If certain cells only contain a first name and no additional data our current formula will result in an error. This is because our FIND function was unable to locate a space. However, because of our previous step we know their is data in the referenced cell or it would have remained empty and not produced an error.

So we are able to assume the referenced cell just contains a first name.

Additional Function

• IFERROR

We know if an error is produced by our current formula then the cell just contains the person’s first name. By using the IFERROR function to provide content for the cell if an error is produced. The formula will return the data from A1 if our formula produces an error.

=IFERROR(IF(ISBLANK(A1),, LEFT(A1,FIND(" ",A1)-1)),A1)

Separate Last Name

Separating the last name or word from a cell presents its own unique challenges. To accomplish pulling only the final word or name from a cell we are going to insert additional spaces between each word, then extract data starting from the right characters within the referenced cell, and finally trim the content to a single word.

=IF(ISERROR(SEARCH(" ",A1)),,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))))

Formulas Used

• LEN
• REPT
• SUBSTITUTE
• RIGHT
• TRIM
• SEARCH
• ISERROR
• IF

Formula Explained

By establishing the goals of our last name formula we can construct a formula to accomplish our goals. The first goal of our formula is to separate each word enough to allow us to extract characters starting from the right side of our referenced cell. This formula will produce the last word or name of cell A1.

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))

1. The first step in this formula is to use the LEN function to find the number of characters in cell A1. LEN will return a numerical value of all characters including spaces in the referenced cell. Example: “Simplify Sheets” would return a LEN numerical value of 15.
2. Next we will use the REPT function to repeat a specified value, in this case a space, by the numerical value we found in step 1.
3. Use the SUBSTITUTE function to replace all instances of spaces in the referenced cells. This will now separate each word in the referenced cell by the same number of spaces as the numerical count of characters in the referenced cell. Example: “Simplify Sheets” would now have 15 spaces between the two words.
4. With the RIGHT function we are able to extract data starting from the right side of a cell by the number of specified character count. By setting the character count to LEN of the referenced cell ensures we are only extracting the last word or name from the referenced cell. Example: Since we now have 15 spaces between “Simplify Sheets” and we extract 15 characters from the right we extract “Sheets” with 9 spaces in front of our last word. The word “Sheets” is 6 characters, which is why we have an extra 9 spaces at the beginning of our extracted value.
5. To remove the extra spaces from the beginning of our extracted value we use the TRIM function. TRIM removes leading, trailing, and repeated spaces in text.

Let’s expand our formula to address a few potential issues. In our first name formula we accept that if there is only one name in the referenced cell than it is the first name. In that case we will want to leave the last name cell blank.

Additional Functions

• SEARCH
• ISERROR
• IF

There are 2 potential issues we hope to address by expanding our function.

1. If the referenced cell only contains a first name.
2. If the name cell is blank.

=IF(ISERROR(SEARCH(" ",A1)),if_ture_value,if_false_value)

Formula Explained

1. By using the SEARCH function we are able to search for a specific character within a referenced cell. By searching for a space if the referenced cell doesn’t contain a space it is either blank or a first name and will return an error.
2. With the ISERROR function we are able to produce a boolean statement; if an error value is returned than this function produces a TRUE statement. If an error is not returned then it produces a FALSE statement.
3. With an IF function we can specify values or formulas to run.

Combining our formulas

We can now combine our previous two formulas to produce a single formula that will return the last name or word in a cell, ignore blank cells, and return an empty value if the referenced cell only contains a first name.

=IF(ISERROR(SEARCH(" ",A1)),,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))))