How to Separate First and Last Name in Google Sheets

If you are working on Google Sheets as your online spreadsheet editor, you might need to create a list of full names at some point. In such a list, you’ll need to separate those names into first and last, but to do so; you are not quite sure what steps to take.

Fortunately, Google Sheets offers an easy way to automatically separate first and last names; plus, you can choose from several methods based on your preferences.

Read this blog post to learn how to split names in Google Sheets.

You can also check out: How to indent in Google Sheets?

How to split first and last name in Google Sheets

By using the SPLIT formula

Google Sheets has a feature that enables users to split their text instantly, based on the delimiter, which is a space character. This feature/function is named SPLIT, meaning that the formula uses the space character to split the full name into first and last.

The SPLIT FORMULA IS: “=SPLIT(A2,” “)”

Once you enter this formula in the B2 cell and press ‘Enter’, the full name in that cell will automatically split into first and last (the first name in cell B2 and the last name in cell C2).

These are the steps to properly use this formula:

1. Select the cell to put the SPLIT formula “=SPLIT(A2,” “)”.
2. Ensure that the following column is blank to make space for the last name to be placed.
3. Type in the formula mentioned above and hit 'Enter'.
4. Once the first and last names appear in the separate columns, drag the formula down or copy-paste it to cover the whole list.

You can proceed by copying and pasting the formula in all cells in column B, and by default, it will fill columns B and C with first and last names.

If you don’t want to copy the formula manually, you can fill the handle and drag it to all the cells to which you want the formula to apply.

Important Note: When you use the above-mentioned formula, Google Sheets does not allow you to delete only the last name of any name. You can select to delete the first name in Column B or both first and last name but not just the last name.

By using the ‘Split Text to Columns’ tool feature

This option allows you to specify the delimiter you want to use when splitting first and last names.

1. Select and copy the cells that contain the full names you want to separate.
2. Click on the ‘Data’ tab (upper toolbar menu).
3. Next, click on the option ‘Split Text to Columns’.

4. This will automatically split the names, but you still need to choose a Separator before the split shows.
5. From the Separator dropdown menu, select the ‘Space’ option as the delimiter (this will separate the first and last names into separate cells).

Apart from the SPLIT formula we shared above, this one is useful when you have a list that you only want to split the text once or twice.

By using the 'text' function formula

When using the ‘text’ function for splitting, it allows you to split the entire text. This method is excellent because you can only extract that part of the text you want.

To take advantage of this function, use the formula:

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

With this formula, Google Sheets uses the FIND function to get the position of the space character; for example, in the name Marisa Darcy, the space character is in the seventh position to split the first from the last name.

Or, if you only want the last name, you can also use the formula:

“=RIGHT(A2,LEN(A2)-FIND(“ “,A2))”

This formula is similar to the previous, and in this case, since we need to get the last name, we need to find the number of characters to get after the space character.

So, you should use the FIND function to position the space character and then use the LEN function to find how many characters are in the name.

All in all, the Text functions are very flexible, and you can play with it the way you want, for example, if you have a mixture of names such as first, middle, and last.

Apart from this, you can also explore other things in Google Sheets, like how to remove gridlines, how to sort Google Sheets by date, or how to color every other row in Google Sheets.