Written by Tanja Google Workplace / Sheets
You can separate first and last name in Google Sheets by using these three methods:
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?
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:
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.
This option allows you to specify the delimiter you want to use when splitting first and last names.
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.
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.