There are three methods to split first and last names we will go through today. The first and easiest method is using the split function so we will start there.
| # | Method |
|---|---|
| 1 | Split Function |
| 2 | Split Text To Columns |
| 3 | LEFT & RIGHT Functions |
There is of course one more method, you can manually split first and last names if you only have a few names to work with. However, learning how to split names using functions will enable you to quickly separate them no matter how large your data set is.
Split Function
The simplest way to separate first and last names is by using the split function. This will separate first and last names into individual columns. In this example, we will use it to split each name into its own cell whenever a space is detected.
Example #1
When the function runs, it will find the name "Jerry Fitzgerald" in cell A1 and look for a space where it will split the name.
When this happens, Jerry will remain in cell B1 and Fitzgerald will be inserted into C1.
Example #2
Let's look at an example where that names are formatted slightly differently. If you are working with hyphenated names like Anne-Marie and would like to split them you can use "-" as the delimiter instead of a space.
We can then drag the formula to repeat the process for the rest of the names in the list.
Example #3
In our third example, we will look at an example where we want to split on both spaces and hyphens. This will enable us to separate first names and double-barrelled surnames.
In this example, we use two delimiters, a space and a hyphen and function will split when it sees either of them.
When we copy the formula down we get the expected result!
How The Split Function Works
The split function has two required parameters and two optional ones. However, if we do not include the optional values, Google Sheets will use their default values.
Text
The Text argument specifies the text that needs to be split. It can be inserted directly into the function, alternatively, a cell reference can be inserted.
Delimiter
The Delimiter argument specifies the character (or characters) where the Text should be split. In our example, we used a space but you may use letters, numbers, or characters like commas, semi-colons, hyphens, and underscores.
Split_By_Each
Determines whether the split happens on every occurrence of the delimiter. In its default state, it is set to TRUE meaning every time the delimiter is found, a split will occur. However, if you would like to split once, this parameter can be set to FALSE.
Remove_Empty_Text
When an empty cell is created by the split function, the remove_empty_text parameter will determine whether is removed or not. TRUE is the default state of this optional parameter and will mean empty cells will be removed. If you would like to keep the empty cells, set this value to FALSE.
Split Text to Columns
You can also use the Split text to Columns in Google Sheets to split first and last names. This is useful if you have a list of people’s names and need to separate their first and last names into two columns. If you don’t have any other information about the people, this method is usually better than regular expressions, which can complicate matters.
You can use the tool to split your data into separate columns based on the value in the first row of your data. Here is how you do it.
- Highlight the cells with the first and last names you would like to split.
- Go to the Data menu and click on Split text to columns.
- You will then be asked to select a Separator. In our case we will select space.
4. The first and last name will then be split into adjacent columns
LEFT & RIGHT Method
Our final method for splitting first and last names in Google Sheets is using the LEFT and RIGHT functions. These are two separate functions that will need to be written individually.
- Copy the formula "=LEFT(A1,FIND(" ",A1)-1)" and paste it onto your sheet. In this example the names to be split are in cell A1. If your first name is in a different cell, you may need to adjust the cell reference.
- Copy the formula "=RIGHT(A1,LEN(A1)-FIND(" ",A1))" and again paste it into your sheet. This function will separate the second name.
When you drag both formulas, you should have separate columns of first and second names.
How The LEFT and RIGHT Functions Work
The LEFT method is simple: it takes whatever name you wish to split and finds the space where you are trying to split is. The RIGHT function works in a similar way, except this time we are splitting from the other side.
The LEFT function will return the characters to the left of the space, while the RIGHT function will return the characters to the right.
LEFT, RIGHT, LEN and FIND
These formulas combine the LEFT, RIGHT, LEN, and FIND functions. The LEFT and RIGHT functions help extract a substring of a specific text from the beginning and the end, respectively.
The LEN function will return the count of all characters used, including all delimiters - which is the reason we may need to add or subtract from the value returned.
The FIND function is helpful in locating a specific character within the string. The function returns the position of the first occurrence searched within the series. In the formula =FIND ( " ", A3 ) ), the FIND function locates the space character in the string. You may then subtract one to return the number of characters used in the first name.
Conclusion
When splitting first and last names in Google Sheets, you have 3 methods to choose from—SPLIT, Split text to columns, and the LEFT/RIGHT functions. The split method is the best choice when splitting a long data string into multiple columns based on different delimiters included in the string. If you are starting out we recommend the SPLIT method as it contains all the functionality required for most use name splitting use-cases.
