Skip to content

How To Split First And Last Names In Google Sheets

Splitting first and last names in Google Sheets can be a frustrating process. Fortunately, the methods below make the process simple.

Split First and Last names in google Sheets cover photo

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.

Split function example

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.

Showing split function in use

When this happens, Jerry will remain in cell B1 and Fitzgerald will be inserted into C1.

Showing the result of a split function

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.

spliting names where they are hyphenated

We can then drag the formula to repeat the process for the rest of the names in the list.

More examples of splitting hyphenated names

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.

using two delimiters to split first and second names

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.

Full split function


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.


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.


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.


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.

Split text to columns to separate first and last names in google sheets
  1. Highlight the cells with the first and last names you would like to split.
  2. Go to the Data menu and click on Split text to columns.
  3. 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

Separating text to columns using the space separator


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.

Left function to split names in google sheets
  1. 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.
  2. Copy the formula "=RIGHT(A1,LEN(A1)-FIND(" ",A1))" and again paste it into your sheet. This function will separate the second name.
right function to split first names and last names in google sheets

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.


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.


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.



As an Amazon Associate we earn from qualifying purchases.