What Is Text To Columns in Google Sheets?
Text to Columns in Google Sheets means converting any selected text to columns in order to organize and analyze data with ease. There are various methods to split text into columns with delimiters like spaces, commas, or other special characters. Before we split the text into columns, one must make sure the data is properly formatted.
Here, the data should be organized into rows and columns and the text to split should be present in a single column. One of the methods is using the SPLIT function or the Text to Columns feature. Let us see how the following data is split using the SPLIT function.
=SPLIT(A1, “ “)
Key Takeaways
- Text to Columns in Google Sheets is converting any selected text to columns to organize and analyze data efficiently.
- There are different ways to do this:
1. Using the SPLIT Function
2. Utilizing Text to Columns Feature
- To use Text to Columns, select the range of cells to split and go the “Data” menu. Select “Split text to columns.” Next, choose the delimiter to use to split the text, such as a space or comma.
- The SPLIT function formula would split the text into different cells column wise. The delimiter can be specified as required.
How To Use Text To Columns in Google Sheets?
When working with data for analysis or calculations, you may have to split text into separate columns. There are two ways to do this. One is using the SPLIT function or the Text to Columns feature.
#1 – Using the SPLIT Function
The syntax of the SPLIT function in Google Sheets is as follows:
=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
- text – The string to divide.
- delimiter – The character to use to split text.
- split_by_each – It specifies if we should divide text around each character present in the delimiter.
- remove_empty_text – If TRUE, the empty text will be removed. If FALSE (default), empty text is kept.
For example, to split a cell containing the text “Tom and Jerry”, and you want to split it into separate cells, use the following formulas could use the following formula:
=SPLIT(A1,” “)
This formula would split the text in cell A1 into three different cells column-wise. The delimiter in this case is a space, which splits the text wherever there is a space.
Not just space, you can supply any delimiter to split the text.
You can split the following data :Adam,26,M into different sections, use the following function.
=SPLIT(A3,”,“)
This formula would split the text in cell A3 into cells in different columns, with “Adam” in one cell, “26” in another, and “M” in the third. The delimiter in this case is a comma, which splits the text wherever there is a comma.
#2 – Utilizing Text to Columns Feature
Another way to split text in Google Sheets into columns is to use the Text to Columns feature in Google Sheets. It allows you to split a range of cells into separate columns based on a specified delimiter.
To use Text to Columns, you must select the range of cells to split. Afterwards, click on the “Data” menu. Select “Split text to columns.” You get a pop-up window. Next, choose the delimiter to use to split the text, such as a space or comma.
Click “Split” to split the text into separate columns. Sheets will automatically create new columns for each section of the split text.
Google Sheets provides a built-in tool to split cells called Split text to columns. It is simple to use for anyone and allows you to split text into separate columns using delimiters. Let us look at the steps involved.
Step 1: Select the cells containing the data that you want to split.
Step 2: Now, go to Data > Split text to columns. Proceed to the next step.
Step 3: As soon as you select this, you get a separator detector option where you can choose your separator type or choose “Detect Automatically.”
Some common separators it offers include period, comma, semicolon, or space. We use the option “Detect Automatically.” Google Sheets will immediately split the data into separate columns. You can also choose Custom and enter your own delimiter.
Examples
In Google Sheets, “Text to Columns” is a feature that allows you to split the contents of one cell into multiple cells based on a delimiter or fixed width. It’s often used to break down data that’s stored in a single cell, such as separating a full name into first and last names or splitting a list of items into individual columns.
Example #1 – Using Text to Columns With Dates and Numbers
In this column, we have a column with dates. They are of the format DD/MM/YYYY. We will try to split them into columns for the day, month, and year. Let us look at the steps to do the same.
Step 1: Select the cells containing the dates, which are A2:A7. Now, go to the Data menu and choose Split text to columns.
Step 2: Choose the Custom delimiter option and select the “/” as the delimiter.
Step 3: All the data will be split into three columns:
- Column A contains the Day,
- Column B contains the Month,
- Column C contains the Year.
Thus, you can use each part individually for any calculations or analysis.
Example #2 – Parsing Product Information
Parsing product information from a single column into multiple columns is useful when you have all the product details stored in a single cell and want to break them down into separate columns. Here, the data is of the form (product name-price-size).
Step 1: Let us look at the data below. We use Google Sheets SPLIT function to separate the product details into different columns. Enter the following function in cell B2.
=SPLIT(A1,”-“).
Step 2: Press Enter and drag the formula up to cell B6.
Observe how the text has been split with the SPLIT function.
Example #3 – Organizing A Contact List
We have some data in which we have a contact list of some names and their contact numbers and city name. The details are shown below. We receive this information from a CSV file separated by commas and must split the details in separate columns. Let us look at how to do the same. We will apply the formula at once using ARRAYFORMULA function.
Step 1: First, we use the function SPLIT in Google Sheets to separate the details.
=SPLIT(A1:A7,”,”).
Step 2: Next, we add the ARRAYFORMULA function to apply it to the entire range. Enter the formula in B1.
=ArrayFormula(SPLIT(A1:A10,”,”))
Step 3: Press Enter. You can see how the data has been split into three columns.
Here, ARRAYFORMULA in Google Sheets allows you to perform calculations on entire ranges instead of just individual cells. It is beneficial, especially when you have very large datasets with thousands of rows.
Important Things to Note
- SPLIT can be combined with functions like ARRAYFORMULA, and QUERY for complex manipulations.
- Google Sheets also offers a Fixed width option that is useful when you have to split data based on character positions (e.g., splitting a date like 20250303 into 2025, 03, and 03).
- When using SPLIT, ensure that the target range to display the results does not overlap with other data to avoid the #REF error.
- Google Sheets can sometimes automatically detect and split based on the most likely delimiter
- We commonly use split cells in Google Sheets when parsing CSV data, separating addresses and full names into first and last names, and so on.
Frequently Asked Questions (FAQs)
Some important points to be considered when using the Split Text to Columns option include that you should ensure that there is no data in adjacent cells. This feature always overwrites your data. Remember that the Split Text to Columns tool overwrites your original column with the split data and the adjacent columns.
It also does not allow you to use multiple delimiters. The feature only splits by one separator at a time. Google Sheets doesn’t split cells by multiple delimiters simultaneously.
There are different ways to convert text into columns.
The first option is to use the “Split text to columns” option under the Data tab.
Other options include:
• Using SPLIT function
• Using SPLIT along with ARRAYFORMULA
To split a date or timestamp into separate components like day, month, and year, we can use the SPLIT() function if it’s formatted with delimiters. Else we can extract the components using date functions like DAY(), MONTH(), YEAR(), HOUR(), and MINUTE().
Download Template
This article must help understand Text to Columns in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is Text to Columns in Google Sheets. We learn how to convert text to column by using the text-to-columns feature with examples. You can learn more from the following articles. –
Leave a Reply