A lot of times, you may want to split the text strings into two or more columns in Google Sheets.
For example, if you have names, you may want to split the first name and the second name.
Similarly, if you have an address, you may want to split the address elements (such as house numbers, street, city, state).
Split Text in Google Sheets
In this tutorial, you'll learn how to split text in Google Sheets:
- Using the SPLIT function.
- Using the Text to Column feature.
Split Text in Google Sheets Using the SPLIT Function
Here I have a list of some of my favorite fictional characters.
To separate the first name and last name, use the following formula in cell B1:
This would instantly split the name and give the first name in cell B1 and last name in cell C1. Now copy paste it for all the cells or drag it down to apply the formula.
Here is how it works:
SPLIT function takes two arguments:
- The first argument is the text which you want to split.
- The second argument is the delimiter that is used to separate the text strings.
In the above example, the delimiter is a space character.
NOTE: The benefit of using this method is that it is dynamic. If you change the name in any cell, the result would dynamically update.
Also, you won't be able to delete a part of the result. For example, you can not delete only the last name.
Split Text in Google Sheets Using Text to Column Feature
Another quick way to achieve the same result is using the Text to Column feature in Google Sheets.
Again let's take the same example of fictional characters.
Here are the steps to split the first name and the last name:
- Select all the cells in which you want to split the text.
- Go to Data –> Split text to columns.
- In the Separator drop-down. select the delimiter (which is space in this example).
This would instantly split the names into first name and last name.
NOTE: This technique alters the original data set. If you want to keep the original dataset intact, make a copy. Also, the results are not dynamic, which means that if you make changes, you'll have to repeat this process again.
You May Also Like the Following Google Sheets Tutorials: