In this tutorial we will learn how to apply the split text to columns functionality in Google Sheets to different types of text. We will also have step-by-step examples using the most common types of data so you can see how it works. Enjoy reading!
How to Split Text to Columns in Google Sheets
1. Select the range of cells with text to divide
The range should ideally be a single column with several rows of entries. The adjacent columns should be blank as well
2. Click Data then select Split text to columns
The Data option is in the main menu at the top of the page. The Split text to columns option is near the end of the dropdown list.
3. Select the separator
Separators are special characters that mark where you can separate the string to its parts. Google Sheets can automatically detect the separator to use from the data stored in the column you selected, just like this case.
If Google Sheets did not automatically detect the separator, you can still set it as it contains a list of characters commonly used as separators. They are commas, periods, semicolons, and spaces. We will select Comma for this example.
After selecting the separator, just click anywhere in the sheet to keep the changes.
Example: Splitting Full Name
Full names use spaces as separators. Here are the steps:
1. Select the range of cells containing the full names
The range should ideally be a single column with several rows of entries. The adjacent columns should be blank as well.
2. Click Data then select Split text to columns
The Data option is in the main menu at the top of the page. The Split text to columns option is near the end of the dropdown list.
3. Select Space as the separator
Click the drop-down box, then select Space.
The output becomes:
Example: Split Email
You can split the email account username from its host. Here are the steps:
1. Select the range of cells containing the email addresses
The range should ideally be a single column with several rows of entries. The adjacent columns should be blank as well.
2. Click Data then select Split text to columns
The Data option is in the main menu at the top of the page. The Split text to columns option is near the end of the dropdown list.
3. Select Custom then type the ampersand symbol @ as the separator
The ampersand connects the email user ID with the host holding the email account. The ampersand is not listed as one of the default choices. You can still set it by clicking the drop-down box, then select Custom.
Afterwards type the ampersand symbol @ to the textbox.
Once the sheet updates, just click anywhere on the sheet to retain the changes.
Example: Split URL
You can split URLs into the main website and its subdirectories. They are marked Here are the steps:
1. Select the range of cells containing the URLS
The range should ideally be a single column with several rows of entries. The adjacent columns should be blank as well.
2. Click Data then select Split text to columns
The Data option is in the main menu at the top of the page. The Split text to columns option is near the end of the dropdown list.
3. Select Custom then type the slash symbol / the separator
The slash marks the hierarchy of subdirectories of a website, like a series of folders inside another in our files. However, the slash is not listed as one of the default choices. You can still set it by clicking the drop-down box, then select Custom.
Afterwards type the slash symbol / to the textbox.
Once the sheet updates, just click anywhere on the sheet to retain the changes.
Recommended articles
More resources
