How to Separate the First and Last names in Google Sheets

Reading Time: 5 minutes

Separate the first and last names in Google Sheets using the SPLIT function.

Syntax
=SPLIT(name, delimiter)

name: It represents the text input that one wants to split into two or more columns.
delimiter: It represents the character on the basis of which the text is to be split.

Sample Usages
=SPLIT("Constantine Levin", " ")

//This splits “Constantine” from “Levin” and places them in different cells. Here we use space as the separator.

=Split(A4," ")

//Separates the first name and the last name in cell A4.

Sample of the spreadsheet

There is no limit to the things you can do with Google Sheets. The extent to which one can clean their data or derive charts from the data in Google Sheets is incredible. One such situation where you may have to modify your data to suit your purpose could be when you want to separate the first and last names in Google Sheets.

In this article, you will learn two ways to separate the first and last names in Google Sheets.

You can make a copy of this spreadsheet and follow along with the tutorial. The data sample as well as the formulas are mentioned.

Method #1: Using the Split Function

Formula/Syntax for the function

The formula to use the SPLIT function in Google Sheets is shown below:

=SPLIT(name,delimiter)

The SPLIT function takes two compulsory arguments which are explained in detail below:

  1. name: It represents the text input that one wants to split into two or more columns.
  2. delimiter: It represents the character on the basis of which the text is to be split.

Note: Both these arguments are string inputs, hence they must be enclosed within inverted commas, if inputted manually, or formatted as text in case, cell referencing is used.

Separate the first and last names in Google Sheets using the SPLIT function

Let’s see how to split first and last name in google sheets:

Step 1: Enter the data in a table-like format as shown below.

Sample dataset
Sample dataset

Step 2: In cell B2, insert the following formula:

=Split(A4,” “)

Since the first and last names are separated by a space, we enclose a single space within the double inverted commas while specifying the delimiter in the SPLIT function.

Applying the SPLIT formula in cell B4
Inserting the formula

Step 3: Press enter and separate the first and last names in Google Sheets. You will notice that the name “Channing Tatum” has been split into “Channing” and “Tatum” in the adjacent columns.

Displays the result after inserting the formula
Displays the result of the formula

Step 4: Copy and paste the formula into the cells below or drag the bottom right of the cell containing “Channing” to the end of your data.

Separate the first and last names in Google Sheets using the split function
Separate the first and last names in Google Sheets using the Split function

In case, the first and last names were separated by any other character such as “,”, “@”, etc., we would have to replace the delimiter in the split function with the new character.

Moreover, even if the first and last names are separated by more than a single space, a single space can be used as a delimiter to get the appropriate result as shown below

When different delimiters are used in the split function to separate the first and last names in Google Sheets
Using different delimiters in the Split function

Method #2: Using Split Text Into Columns Feature

Let’s use the same data as above but a different method to separate the first and last names in Google Sheets.

Step 1: Enter the list of names you want to split

Sample database for method 2
Sample dataset

You will notice that some names contain extra spaces before them while some contain more than one space between their first and last names. To remove these extra spaces we will use the TRIM function.

Step 2: Enter the following function, in an empty cell beside the original sample dataset.

=Trim(A4)

The trim function removes the extra spaces present in the value which is enclosed within the parentheses.

Using the trim function to remove any extra spaces in the dataset
Using the trim function to remove any extra spaces in the dataset

Step 3: Copy and paste the function in cell A4 to include the entire dataset. The new data should look as follows:

Result after using the trim function
Result after using the Trim function

Step 4: Select the new data. Go to Data→ select split text to columns

Navigating to the split text to column option by selecting the data inputted and clicking on the data tab.
Navigating to the split text to columns option

Step 5: A small box would appear at the bottom right of the selected data. Click on the box and select the appropriate delimiter. In our example, the delimiter would be space. 

A list of different pre-defined delimiters in Google Sheets
Dialogue box after clicking on the split text to column option

You can also input your own custom delimiter by clicking on the “custom” option.

Step 6: Click “Space”. You will notice that the column containing full names has been separated into first name and last name as shown below.

Separate the first and last names in Google Sheets using the split text to columns option
Separate the first and last names in Google Sheets using the split text to columns option

What if we had not used the TRIM function?

If we had not used the trim function, Google Sheets would jump to a new column every time it discovered the delimiter in question and the output would have been incorrect.

Consider the following dataset:

Dataset before using the trim function
Sample dataset before using the trim function

Let’s split the text into columns and see the result.

Displays the result if trim function hadn't been used
Splitting text into columns without using the trim function

If you are more interested to know about how to use the split text to columns option, click here.

Thus, it’s always ideal to use the TRIM function before we try to separate the first and last names in Google Sheets using the split text to columns option when the concerned delimiter is a space.

Conclusion

The drawback of the split text to column function is that it overwrites the original data. To solve this, one would have to copy the original data into a new column and perform the operation. Thus, if you are in a hurry and want to compare the new result with the original data, it’s recommended that you use the SPLIT function.

To read about more such interesting methods to complete your task, refer to our blog.

See Also

Here are a few other related tutorials that you can go through to enhance your knowledge and be faster when using Google Sheets.

Use LEFT and RIGHT function in Google Sheets

How to use the Query function in Google Sheets

How to use the T function in Google Sheets