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.

• Method 1: Using the SPLIT function
• Method 2: Using Split text into columns feature

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.

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.

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.

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.

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

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

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.

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

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

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. 

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.

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:

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

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