If you have lists of people’s Full Names in your Google Sheets, sometimes, you might want to separate them as first and last names.
Usually, I split names when I need only one of them for my data. It also makes my data a lot more clean and organized. For that, I mostly use the Split Text to Columns feature.
In case you find yourself in such a situation, I have discussed all possible ways to separate names.
Use SMART FILL Feature
For smaller data, you can use Google Sheets’s amazing feature called “Smart Fill.” Here, all you need to do is enter the Names in a Separate Column.
Then, once the Sheet recognizes the pattern, click on the Tick option to accept the SMART Fill. It’ll automatically fill the names for the entire column.
Repeat the Same for another column too.
Use Split Text to Columns
In Sheets, another easiest way to separate names is by using the Split text to columns feature. Personally, this is my favorite tool as it is pretty simple to use and gets the job done quickly—for huge datasets.
This method is best when a delimiter like space, comma, semi–colon, full stop, etc. separates your names.
- Firstly, Select the column with names.
- From the Data Tab, click on Split text to columns.
- On the bottom-right, expand the Drop-down and choose a Separator. Here, I picked Space. You will have the names in separate columns now.
Use SPLIT Function
Next, there is also a formula version for the “Split Text to Columns” tool called the “SPLIT” Function.
It is best used to split the Names that contain a delimiter.
But, why use a formula when the functionality is exactly the same?
Well, this is because while the “Split Text to Columns” is an easier option, the SPLIT function is dynamic. Whenever you edit values, it’ll automatically update in the result cell.
I recommend this method for users who still need to change their values.
| Function | Syntax | Arguments |
| SPLIT | SPLIT(text, delimiter, [split_by_each], [remove_empty_text]) | text: the value you want to split. delimiter: specify the character to divide the text. split_by_each: split each character with a delimiter. remove_empty_text: Specify a boolean value. Enter TRUE to ignore the blank cells in the text. Choose FALSE to include empty cells. |
As an example, I will split texts having the delimiter Space, Dash, and Comma.
| Example | Name | Formula | Description |
| Split cells with Space | Jake Nolan | =SPLIT(B4, ” “) | In the formula, the SPLIT function separates the name with space. |
| Split cells with Dash | Lara-Hood | =SPLIT(B5, “-“) | This time, we have entered the “-” delimiter in the formula. |
| Split cells with Comma | Jaime, McCann | =SPLIT(B6, “,”) | Here, the formula divides the name with a comma. |
Using LEFT, RIGHT, FIND Formula
So far, we’ve discussed how to split the first and last names together in Sheets. But, say you find the need to separate the names based on position.
To do that, we will nest LEFT, RIGHT, and FIND functions together.
Before we start, let’s quickly look into the functions and their syntax, shall we?
| Function | Syntax | Description |
| LEFT | =LEFT(string, [number_of_characters]) | Returns the characters from the start of a text string i.e. from the left side. |
| RIGHT | =RIGHT(string, [number_of_characters]) | Returns the specified characters starting from the right part of the string. |
| LEN | =LEN(text) | Counts the total number of characters in a text. |
| FIND | =FIND(search_for, text_to_search, [starting_at]) | Find the position of a text string. |
Separate Names from Left
To extract the names from the left and leave the rest as it is, you can use the given formula.
=LEFT(B4, FIND(" ", B4)-1)
We have specified the formula to return the left characters from the string before the space. That is why we got the first name.
Separate Names from Right
This time, to extract the names from the right, we will be using the RIGHT, LEN, and FIND functions nested together. Here’s the formula you can use
=RIGHT(B4, LEN(B4)-FIND(" ", B4))
The formula returns the characters from the Right after the Space.
Use REGEXEXTRACT Function
Moving on with an advanced example of splitting names, we will be using the REGEXEXTRACT Function for that. This function returns the text strings that match the regular expression.
| Function | Syntax | Argument |
| REGEXEXTRACT | =REGEXEXTRACT(text, regular_expression | text: text string regular_expression: an expression to match the first part of your value with. |
Assuming you have multiple combined values in your Column, like Denver Thomas Lyla Melton in one cell. Now, I can’t use any of the above methods since both names are separated by a space.
So, in order to divide these names into separate cells, here’s the formula I used. You can just edit the cell reference in the formula while keeping the regular expression as it is.
=REGEXEXTRACT(B3, "(\w+(?:\s\w+)?)\s(\w+(?:\s\w+)?)")
This formula splits Denver Thomas in one cell and Lyla Melton in another cell.