What Is Split Cells in Google Sheets?
Splitting text in Google Sheets can be used to divide a string with many words and put each fragment into a separate cell. If your cells contain more than one word, you can break them into separate columns, which will help you sort the data in your table more quickly. Splitting cells is dividing the content from a single cell to multiple cells. There are many different ways of doing this in Google Sheets. One of them is using the SPLIT function in Google Sheets, which has the following syntax:
=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
Another way to split text is using the “Split Text to Columns” option. In the example below, we find that the single-cell text “The quick fox jumped over the brown dog” has been split using the “Split Text to Columns” option in the “Data’ tab. You are asked to select the delimiter, and the text is split.

Key Takeaways
- Split cells in Google Sheets is used to divide the data in a cell and place them in multiple adjacent columns based on the delimiter specified.
- There are different ways to do it. One of the is using the SPLIT function and the other is using the “Split text to columns” option in Google Sheets.
- The syntax of the Split function is =SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
- You can use a single character or set of characters as a delimiter. Some commonly used delimiters include spaces, commas, pipes, etc. However, you cannot split cells using multiple separators simultaneously.
- Combined with functions like QUERY and ARRAYFORMULA, it is a powerful function for data manipulation.
How to Split Cells in Google Sheets?
Google Sheets has different ways of splitting cells into columns with their data. Let us look at the two most common methods.
-Using Split text to Columns options
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. This is in the menu bar.

Step 3: As soon as you select this, you get a separator detector option. Here, 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 “Custom” and enter the ‘|’ symbol. Google Sheets will immediately split the string into separate columns.
-Using SPLIT function
We can also use SPLIT in Google Sheets to split a string with delimiters. The Split cells in Google Sheets formula for SPLIT function is:
=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.
Now, let us look at the steps involved in implementing this function.
Step 1: Click on the cell where you want to type the SPLIT function. Here, it is B1.

Step 2: Start typing the function as follows =SPLIT(.

Step 3: Enter the parameters for the function. Here, we write =SPLIT(A1, “ “). Close the parentheses. Here, the delimiter is space. Press Enter.

Step 4: You can observe the string being split. Now, drag the formula to cell B4 and observe the result. The function separates my product names by each space and places units in adjacent cells.

Thus, we can use the function easily to split strings.
Examples
We can split columns by using the SPLIT function with other functions like ARRAYFORMULA for more advanced splitting. Let’s look at some interesting examples as shown below.
Example #1 – Split text to columns option
One of the simplest options for splitting cells in Google Sheets is using the built-in tool “Split Text to Column”. Let us look at the table below.

Step 1: First, highlight the cells which you must split. Here, it is the range A1:A9.

Step 2: Now, go to the “Data” menu and click on it. You should choose the option “Split text to columns,” which is used to split the cells into vertical columns marked by a delimiter.

Step 3: You get a separator detector option where you can choose your separator type or choose “Detect Automatically.” Here, we select space.
It splits the string into separate columns.

Step 4: Choose the option and observe the result.

The only drawback of this feature is that it overwrites the original column with the split data, and it may do so in other cells as well.
Example #2 – SPLIT function
The SPLIT function is one of the best and most reliable options for splitting text into adjacent columns with a delimiter. Let us look at how to split the sales details of three months of some stores across cities and again rejoin them with another delimiter. The sales details are shown. We need to split the sales of each store for that quarter. Let us find out how to get them.

Step 1: First, we use Google Sheets’ innermost function, SPLIT, to separate the cities and their monthly sales.
=SPLIT(A1:A6,”,”).
Step 2: Next, we must use the TEXTJOIN function to join the values with a different delimiter. We can first split and then use the direct formula along with the SPLIT function.
Enter the following formula in B1.
=TEXTJOIN(” ### “, TRUE, SPLIT(A1, “, “))

Step 3: Press Enter and drag the formula up to cell B6.

Observe how the text has been split and joined again using TEXTJOIN and a different delimiter.
Example #3 – SPLIT function+ ARRAYFORMULA function
We have some students and their marks secured in Physics and Chemistry. The details are shown below. We receive this information from an automated program separated by commas and must split the details in separate columns. Let’s see how to do it.

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:A7,”,”))

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.

You can see the marks of the students in Chemistry and Physics split into different columns.
Example #4 – SPLIT function + QUERY function
Let us use the SPLIT function in Google Sheets in combination with QUERY to filter out data based on split items. The QUERY function Google Sheets is very versatile and helps you use commands to manipulate your data in Google Sheets.
Its syntax is as follows:
=QUERY(data, query, [headers])
- Data – the range to analyze
- Query – the query to run
- Headers: says how many header rows are there(optional)
Step 1: Now, consider the table shown below in Google Sheets, where we have entered the data.

Step 2: Enter the SPLIT function as follows:
=SPLIT(A1, “ “).
Step 3: Now, to get the result at once, we use the arrayformula. Enter the following in Cell B1.
=ARRAYFORMULA(SPLIT(A1:A9,” “)). Press Enter.

Step 4: You observe how the data has been split as expected using the single formula. Now, let us type the following Query in cell D2.
=QUERY(B:C,”SELECT B WHERE C = ‘Vegetable'”, 0)
Here,
- The range we are considering is B:C.
- The query within double quotes asks to display all the values in Column B with “Vegetables” as the corresponding values in Column C. Basically we are displaying the vegetables in the list.

When you press Enter, you get the list of vegetables.
Imortant Things to Note
- SPLIT can be combined with functions including INDEX, ARRAYFORMULA, and QUERY for complex manipulations and analyses.
- When using SPLIT, ensure that the target range to display the results does not overlap with other data to avoid the #REF error.
- 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.
- Delimiters in SPLIT are case-sensitive. Remember, if you use a delimiter like “b,” it splits on only the lower-case b’s and not ‘B.’
Frequently Asked Questions (FAQs)
Yes, there are a few drawbacks when using the Split Text to Columns option. It always overwrites your data. When you use it, it also overwrites your original column with the split data and adjacent columns as well.
Another drawback is that you cannot use Google Sheets split multiple delimiters. It only splits by one separator at a time. Google Sheets doesn’t split cells by multiple delimiters simultaneously.
In Google Sheets, there are different ways of splitting cells into columns.
The first option is to use the “Split text to columns” option under the Data tab.
Other options include:
With the SPLIT function
Using SPLIT along with ARRAYFORMULA
Using SPLIT along with QUERY
Some common mistakes that lead to errors include:
1. Choosing the wrong delimiter: It would cause an incorrect split without showing any specific error.
2. Incorrect allocation of number of columns: When splitting cells, you must remember the correct number of columns or rows that will be required. Otherwise, there are chances of your data getting overwritten or you will get an #REF error if you use SPLIT.
3. Correct formatting: Format your data accurately after splitting cells. For example, adjusting the column width for proper display, displaying dates in the right format, etc.
Download Template
This article must help understand Split Cells in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Split Cells in Google Sheets. We learn how to add Split Cells in Google Sheets, charts, examples, working template. You can learn more from the following articles.
Leave a Reply