Separate Names (First and Last Name) in Google Sheets

You can write a formula in Google Sheets to separate the first and last name from a cell. And of writing these formulas, you need to combine a few functions. Now in this tutorial, we will learn to write these formulas.

Separate the First Name

For this, you need to combine LEFT and the SEARCH function.

1. First, enter the LEFT function in a cell.
2. Next, in the first argument, Refer to the cell with the full name.
3. After that, in the second argument, enter the search function.
4. Now, enter a blank space using double quotation marks.
5. From here, in the second search argument, specify the cell with the full name.
6. In the end, enter the closing parentheses, -1, close the left function, and hit enter to get the result.

In this formula, we have used the SEARCH function to get the space position between the first and the last name. And by using that position number from the SEARCH, LEFT extracts the first name from the left side of the cell.

When SEARCH returns the position of the space, for example, 8, LEFT subtracts 1 from it and then extracts 7 characters from the left of the cell, which is the first name.

=LEFT(A2,SEARCH(" ",A2)-1)

Separate the Last Name

You can combine SUBSTITUTE, REPT, LEN, RIGHT, and TRIM functions for this. And can write a formula in the following way.

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

To understand this formula, you need to split it into parts.

We have SUBSTITUTE, REPT, and LEN functions in the first part. In this part, LEN counts the total count of characters in the full name. Then REPT uses that count and returns that number of spaces in the result.

For example, if the left of the full name is 15, then REPT returns 15 spaces in the result. Something like below:

After that, SUBSTITUTE, replace the single space that you have between the names with the spaces returned by the REPT.

In the second part, we have used the right function to get the characters equal to the count of characters of the full name from the right side of the string, which is returned by the first part of the formula.

⮞ Get the Sample Sheet