How to Split a Cell in Google Sheets

Splitting a cell (or cells) in Google Sheets is the most reliable way to divide and study large datasets efficiently. Plus, this helps you save valuable time, improve data accuracy, and perform your calculations flexibly. But how do you split a cell in Google Sheets? This comprehensive guide has got you covered. Understand the whole process of splitting a cell (or cells) with proper explanations and easy examples. 

How to Split a Cell in Google Sheets

1. Split Function

One of the easiest ways to split a cell in Google Sheets is by using its powerful built-in SPLIT function. It’ll help you split a cell (or cells) in various ways. You can first sort the data in Google Sheets by date before splitting a cell.

Below is its quick syntax: 

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

Where: 

• text: The text inside a cell you wish to split.
• delimiter: The character or string separates the text into parts. For example, the cell to be split contains “John-Doe-Smith.” You want to split it into three separate cells based on “-.” In this case, the hyphen (-) will be the delimiter.
• split_by_each: It is an optional argument set to TRUE or FALSE. If set to TRUE, it’ll split the text at each character in the delimiter, and if set to FALSE, it’ll split the entire delimiter string.
• remove_empty_text:  It is an optional argument set to TRUE or FALSE. If set to TRUE, it’ll remove all the empty text from your result, and if set to FALSE, it’ll include all the empty text in your result.

Assuming you’ve understood the syntax and its four elements, let’s use it to split the text “Split a cell” in A1 using the ” “ as the delimiter. As optional arguments, we’ll skip split_by_each and remove_empty_text. 

So, the final formula to be entered in the adjacent cell B1 becomes: 

=SPLIT(A1, ” “)

As shown in the above image, text in cell A1 was split into three other cells: B1, C1, and D1. 

Ensure you have enough empty cells in the row to accommodate each split text. Otherwise, you’ll get an error. Consider locking the cells in Google Sheets after using the SPLIT function to preserve their data.

2. Split Text to Column Function

Besides the conventional SPLIT function, there’s another quick way to split a cell in Google Sheets. For that, you need to use the built-in “Split text to column” function of Google Sheets.

Below are the quick steps: 

1. Select the cell or cells you want to split. Click the Data option from the menu bar, and select Split text to columns from the drop-down list. 
   
2. Choose a separator or delimiter to split the text in A1:A5. You can choose from Space, Comma, Period, or Semicolon.
   
3. Alternatively, allow Google Sheets to detect one for you automatically by selecting Detect automatically. 
   
4. Once done, the content in A1:A5 will be split into the next empty column.   

3. Using SPLIT With Other Formulas

You can also apply the SPLIT function to an entire column in Google Sheets using the ARRAYFORMULA function and exclude unnecessary data using the QUERY function. 

SPLIT Function + ARRAYFORMULA Function

Combining the SPLIT and ARRAYFORMULA functions in Google Sheets lets you split the text into a range of cells at once.

Here’s an example: 

A1:A5 is the range of cells with the target text to be split. Now, enter the following formula in cell B1:

=ARRAYFORMULA(SPLIT(A1:A5, ” “))

As you can see, this is the same SPLIT formula we used above, and we added the ARRAYFORMULA to operate on the entire A column. 

Check the following image for the result: 

SPLIT Function + QUERY Function

The QUERY function is considered one of the robust functions of Google Sheets. It combines the capabilities of several other functions into one, allowing you to run SQL-like queries. Combining it with the SPLIT function helps you keep out unnecessary data while splitting a cell or cell. 

Here’s an example: 

Cell A1 contains the text “Split Cells 01.” In this, we want to leave out 01 in the output. To do so, enter the following formula: 

=QUERY(SPLIT(A1, ” “), “SELECT Col1, Col2”)

Where: 

• SPLIT(A1, ” “) is the regular SPLIT function to split the cell A1.
• SELECT Col1, Col2 selects the first two columns of the Google Sheet to display the split text.
• QUERY is applied to the whole formula to exclude 01 from the output.

Check the following image for the result:

Combine the ARRAYFORMULA function with the QUERY function to change an entire column. That is, =QUERY(ARRAYFORMULA(SPLIT(A1, ” “)), “SELECT Col1, Col2”). If you are splitting a cell for printing purposes, set a print area in Google Sheets to make printing the file easier. 

Be Careful While Splitting Cells in Google Sheets 

We hope splitting a cell in Google Sheets is clear to you. While doing so, be mindful of some common mistakes. These include forgetting to back up the original file, selecting the wrong delimiter, and using an incorrect syntax/formula. If you want to analyze the data further, use ChatGPT in Google Sheets.

FAQs