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.
Table of Contents
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:
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.
Here are the steps to use the Google Sheets Split Text to Columns feature to separate first and last names:
- Select the cell containing the name that you want to split.
- Click the “Data” tab.
- Click on the “Split Text to Columns” option.
- In the “Separator” box that appears, select “Space” as the delimiter.
The above steps would instantly split the full name into first and last names (in different columns).
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.
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:
You can use the below formula to extract only the first name from the full name:
=LEFT(A2,FIND(" ",A2)-1)
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))
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.
- Go to “Extensions” > “Add-on.”
- Select “Get add-ons.”
- Search for “split names” in the Google Marketplace search bar.
- Select the Split Names add-on and click “Install” to download it
- Google might ask you for permission to install the add-on, in which case you can click “Continue” and select your Google account.
- Then click “Allow.”
- Once you have installed the add-on, select the list of names you want to split.
- Go to “Extensions,” and you should see “Split Names” in the menu. Click it, then click “Start.”
- 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.
- Once you’re done, click “Split.” The add-on will take a few seconds to load before splitting your 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.
Conclusion
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:
