How To Separate Names in Google Sheets (3 Easy Ways)

Watch Video – How To Separate Names in Google Sheets using Split Text to Columns

Google Sheets has many formulas and functionalities (some even better than the ones in Microsoft Excel spreadsheets) that can split text.

One such area where you may need some formula intervention could be when you need to separate names into first name and last name (or email address into the username and domain name).

In this tutorial, I will show you how to separate names in Google Sheets with four simple methods.

How To Separate Names in Google Sheets Using the SPLIT Formula

Google Sheets has an inbuilt function called SPLIT that allows you to split the text based on a delimiter.

In the case of names, the delimiter is a space character. This means that the formula uses the space character to split the full name into first and last names (or however many parts are there to the name).

Let’s say you have a data set as shown below:

How to separate Names in Google Sheets—Sample Dataset

To split the full name and separate first and last names in Google Sheets, use the below formula in cell B2 in Google Sheets:

=SPLIT(A2," ")

As soon as you enter this formula and hit enter, it will automatically split the name into first and last names. It will put the first name in cell B2 and the last in cell C2.

You can now copy and paste this formula in all the cells in column B or use the fill handle, and it will automatically fill columns B and C with first and last names.

Note that when you use this formula, Google Sheets will not allow you to delete only the last name of any name. You can delete the first name in Column B or both first and last names, not just the last name.

This is because when you enter the formula in any cell in column B, it automatically adds the one in column C. So the last name is a part of the formula, and since the formula is in column B, you will have to delete it from there only.

One benefit of using the SPLIT function to split names (or any other text) is that it makes your results dynamic. This means that if you change any name, the results will automatically update to reflect the change.

Also, note that this formula will give you all the split values. For example, if you only want the first or last names, you can’t use the SPLIT function.

If you only want to get a part of the text (let’s say only the first name or only the last name), use the TEXT formulas method shown later in this tutorial.

How To Separate Names in Google Sheets Using the Split Text to Columns

Another powerful feature in Google Sheets is the “Split Text to Columns” feature.

Google Sheets Split Text to Columns allows you to specify the delimiter that you want to use to separate columns or text in Google Sheets (names in our example).

You can separate columns using a comma, semicolon, period, space, or custom delimiter.

Google Sheets then automatically uses the specified delimiter and splits the text.

Suppose you have the names as shown below, and you want to separate the name into first name and last name.

Names to Separate using Text Columns feature

Here are the steps to use the Google Sheets Split Text to Columns feature to separate first and last names:

  1. Select the cell containing the name that you want to split.
  2. Click the “Data” tab.
    Click the Data Tab
  3. Click on the “Split Text to Columns” option.
Google Sheets split text to columns


  1. In the “Separator” box that appears, select “Space” as the delimiter.
    Select Space as the delimiter


The above steps would instantly split the full name into first and last names (in different columns).

How to split first and last name in Google Sheets


Unlike the SPLIT formula result, the result you get after using the ‘Split Text to Columns’ is static, as shown above. This is a preferred method when you have a list and you only want to split the text once or twice. If you want to split the text multiple times or repeatedly, using a formula is best.

[adthrive-in-post-video-player video-id=”2hkCXljw” upload-date=”2019-11-18T12:31:33.000Z” name=”How to Separate First and Last Name in Google Sheets (using Google Sheets Text to Columns)” description=”Split Text to Column Google Sheets is a feature that comes in handy when you want quickly split the first name and the last name, or the user name and domain name from email id, or the domain name from URLs. In this tutorial, I will show you multiple examples of how to split text in Google Sheets to columns. To use the Google spreadsheet Split Text to Columns options, go to the Data tab and select ‘Split Text to Columns’. Here are important points to note while using Google Sheet Split Text to Columns: — It overwrites the original data set. If you want to keep the original dataset intact, create a copy of the data set and use Split Text to Column on that data set. — It would give you static results. This means that your data would not update in case you update the original dataset. — Every space character is considered as a different separator.”]

How To Separate Names in Google Sheets Using the Text Function

While the SPLIT function is a great way to split text to columns in Google Sheets, it gives you the entire split of the text.

For example, say you only want the first name or only the last name, then you can’t do it directly with the SPLIT function. You can still use it by converting the formula to values and then deleting the cells you don’t want, which makes it less efficient.

In such cases, you can use the excellent TEXT functions that Google Sheets has. These Text functions allow you to extract only that part of the text that you want.

Let me show you how to separate text in Google Sheets using the Text function with examples.

Suppose you have the data set as shown below:

Names to Separate using Text Columns feature


You can use the below formula to extract only the first name from the full name:

=LEFT(A2,FIND(" ",A2)-1)
LEFT formula to extract the first name


The above formula uses the FIND function to get the position of the space character in the name. For example, in the name ‘Jesusa Owenby,’ the space character is in the seventh position.

Now we use this position number to extract all the characters to its left using the LEFT function.

Similarly, if you only want the last name, you can use the following formula:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))
RIGHT formula to extract the Last name


The above formula uses the same concept with a slight twist. Since we need to get the last name, we need to find the number of characters to get after the space character.

So, I first used the FIND function to get the position of the space character and then used the LEN function to find how many characters are there in the name. A simple subtraction of the FIND formula result from the LEN formula result gives me the number of characters in the last name.

Then I use the RIGHT function to get all the characters from the right (after the space character).

The TEXT functions allow you a lot of flexibility, and you can handle a lot of scenarios with these types of formulas. For example, if you have a mix of first/last names and ones with a middle name, you can still use a variation of these TEXT formulas to get the first and the last name.

How To Separate Names in Google Sheets Using the Split Names Add-on

Like most functions in Google Sheets, you can also use an add-on to split names.

  1. Go to “Extensions” > “Add-on.”
Get addons


  1. Select “Get add-ons.”
  2. Search for “split names” in the Google Marketplace search bar.
Google Workspace Marketplace


  1. Select the Split Names add-on and click “Install” to download it
Split Names add-on


  1. Google might ask you for permission to install the add-on, in which case you can click “Continue” and select your Google account.
Install an addon


  1. Then click “Allow.”


  1. Once you have installed the add-on, select the list of names you want to split.
  2. Go to “Extensions,” and you should see “Split Names” in the menu. Click it, then click “Start.”
Use the split names addon


  1. The split names window has several choices. For our example, we will select the “First name” and “Last name” checkboxes. If you also have middle names, you can add a middle name column.
Select first and last name


  1. Once you’re done, click “Split.” The add-on will take a few seconds to load before splitting your names.
Split names


The Split Names add-on is useful because it can differentiate between first names, middle, and last names. It also recognizes over 80 titles and 40 post-nominals. Unfortunately, it isn’t free, but it has a free 3-day trial. If you want Google Sheets split first and last names, you don’t really need to pay for this add-on since you can use the first three options. However, if you want to split jumbled names into first and last names, then this addon is able to do so.


In this article, we have shown you how to separate names in Google Sheets: Using the SPLIT formula, the Google Sheets Split Text to Columns feature, using the TEXT function, and using the Split Names add-on. Now that you know how to separate names in Google Sheets, you might also be interested in our article that shows how to transpose in Google Sheets.

You may also like the following Google Sheets tutorials:

Most Popular Posts

Related Posts

Thanks for visiting! We’re happy to answer your spreadsheet questions. We specialize in formulas for Google Sheets, our own spreadsheet templates, and time-saving Excel tips.

Note that we’re supported by our audience. When you purchase through links on our site, we may earn commission at no extra cost to you.

Like what we do? Share this article!