Google Sheets provides numerous formulas and features (even better than those of Microsoft Excel spreadsheets) that allow you to split text. In some cases, you may need to intervene with a formula to separate names into first and last names (or email addresses into usernames and domain names).
In this tutorial, I will show you how to separate names in Google Sheets using four simple methods.
Using the SPLIT formula to separate names in Google Sheets
Google Sheets has a built-in feature called SPLIT that allows you to divide text based on a delimiter. In the case of names, the delimiter is a space. This means that the formula uses the space to separate the full name into first and last names (or as many parts as the name contains).
Assuming you have a dataset like this:
To split the full name and separate the first names and last names in Google Sheets, use the following formula in cell B2:
=SPLIT(A2," ")Once you enter this formula and press Enter, it will automatically divide the name into first and last names. The first name will be placed in cell B2, and the last name in cell C2.
You can now copy and paste this formula into all the cells in column B or use the fill handle to automatically fill columns B and C with first names and last names.
Note that when using this formula, Google Sheets will not allow you to delete only the last name from any name. You can delete the first name in column B or both the first name and last name, but not just the last name.
This is because when you enter the formula in any cell in column B, it automatically adds the one from column C. Therefore, the last name is part of the formula, and since the formula is in column B, you need to delete it from there.
An advantage of using the SPLIT function to separate names (or any other text) is that it makes your results dynamic. This means that if you change a 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 to get the first name or the last name, you cannot use the SPLIT function.
If you only want to get a part of the text (e.g., only the first name or only the last name), use the TEXT formulas method described later in this tutorial.
Using the “Split Text to Columns” feature to separate names in Google Sheets
Another powerful feature of Google Sheets is the “Split Text to Columns” function. This function allows you to specify the delimiter you want to use to separate columns or text in Google Sheets (names in our example).
You can separate columns using a comma, semicolon, dot, space, or a custom delimiter.
Google Sheets then automatically uses the specified delimiter to split the text.
Assuming you have the following names and you want to separate them into first names and last names:
- Select the cell containing the name you want to separate.
- Click on 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 will instantly separate the full name into first names and last names (in different columns).
Unlike the result of the SPLIT formula, the result obtained after using the “Split Text to Columns” function is static. This is a preferred method when you have a list and only want to separate the text once or twice. If you want to separate the text multiple times or repeatedly, using a formula is preferable.
Using the Text function to separate names in Google Sheets
While the SPLIT function is a great way to split text into columns in Google Sheets, it gives you the entire split text.
For example, if you only want to get the first name or only the last name, you cannot do it directly with the SPLIT function. You can still use it by converting the formula into values and then deleting the cells you don’t want, but that makes it less efficient.
In such cases, you can use the excellent TEXT functions in Google Sheets. These text functions allow you to extract only the part of the text that you are interested in.
Let me show you how to separate text in Google Sheets using the Text function with examples.
Assuming you have the following dataset:
You can use the following 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 at the seventh position.
We then use this position number to extract all characters to its left using the LEFT function.
Similarly, if you only want to get 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 variation. 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, then used the LEN function to find the number of characters in the name. A simple subtraction of the result of the FIND formula from the result of the LEN formula gives me the number of characters in the last name.
Then, I use the RIGHT function to get all characters from the right (after the space character).
The Text functions offer great flexibility and allow you to handle many scenarios with these types of formulas. For example, if you have a mix of first/last names and first/last names with a middle name, you can still use a variation of these Text formulas to get the first name and last name.
Conclusion
In this article, we showed you how to separate names in Google Sheets using the SPLIT formula, the “Split Text to Columns” feature in Google Sheets, the TEXT function, and the Split Names add-on. Now that you know how to separate names in Google Sheets, you may also be interested in our article that shows how to transpose in Google Sheets.
For more Google Sheets tips and tricks, visit Crawlan.com.
