How to Split Data in Google Sheets Using Functions and Formulas 

Split text to columns in Google Sheets with 4 clicks

In the A1 cell, I have the name of the best-selling car in the US as of October 2024. Let’s split this text to have an automaker and model names in separate cells. 

• Click 1: Navigate to Data => Click 2 select Split text to columns.

• Click 3 & 4: Select the separator (in our example, this is Space) and there you go.

This is the fastest way to divide data in a cell or multiple cells in Google Sheets. It supports 4 default separators (comma, semicolon, period, space) and a custom option that you can specify yourself. The drawback of this method is that it affects the source data. 

If you need to leave the source data unchanged, use the SPLIT function Google Sheets. You can also watch our tutorial on this function on the Coupler.io Academy YouTube channel.

SPLIT function Google Sheets explained

SPLIT divides data into fragments based on the delimiter and puts them into separate cells. The number of cells depends on the number of fragments resulting from the split.

Google Sheets SPLIT formula syntax

=split(data, delimiter, [split_by_each], [remove_empty_text])

• data – Textual or numeric data to split.
• delimiter – Any textual, numeric, or punctuation symbol to split the data around. The delimiter must be specified in quotation marks.
• [split_by_each] – An optional parameter to change the splitting behavior of the delimiter. By default, it’s set to TRUE – the data will be split around each character in the delimiter. For example, if your delimiter is “123”, the data will be divided around “1”, “2”, and “3” separately. If you need to divide the data around the delimiter as a whole, set [split_by_each] to FALSE.
• [remove_empty_text] – An optional parameter, which removes empty text cells from the split fragments. If you want to turn it off, set [remove_empty_text] to FALSE. In this case, you must also specify [split_by_each]  parameter (TRUE or FALSE) in your SPLIT formula.

Check out the options for how to split cells in Excel or how to split Excel sheet into multiple worksheets.

SPLIT Google Sheets formula example

Let’s see how the SPLIT formula handles our example with Ford F-Series. Use the following formula:

=split(A1," ")

Interpretation:

In the A1 cell, we have a text to be split. The delimiter (” “) is space. We don’t need to specify optional parameters, so just skip them. 

You can use the SPLIT function in combination with other functions such as ARRAYFORMULA and QUERY. We’ll explore the cases below.

How to split cells in Google Sheets: real-life examples

Let’s check out some practical cases of using either the SPLIT function Google Sheets or the Split text to columns functionality. This way, you’ll figure out which option suits your case better.

And if you need to load data from your cloud sources to Google Sheets, use Coupler.io. It allows you to import data from 60+ business apps and sources without coding and automate refresh on a schedule. Just select the desired app from the drop-down menu and click Proceed. Sign up for free with no credit card required.

Split text to columns without formulas in Google Sheets

You have a column with values that are needed to be split. Select the column range and apply the Split text to columns functionality as usual. Here is the result:

As you see, we have one issue in row 5 with Tesla Model Y. The reason is that our separator was space, and this value has two spaces:

1 – between Tesla and Model;

2 – between Model and Y.

Split a column with ARRAYFORMULA+SPLIT Google Sheets 

To apply the SPLIT function Google Sheets to the entire column, you’ll need to combine it with ARRAYFORMULA as follows:

=arrayformula(
   split(data, delimiter, [split_by_each], [remove_empty_text])
)

Read more on how you can use ARRAYFORMULA in Google Sheets.

Here is what it looks like in our example:

=arrayformula(split(A2:A11, " "))

How to split cells into rows in Google Sheets 

If you want to split text from your cell or cell range into rows, nest your SPLIT function with TRANSPOSE. Here is the syntax:

=TRANSPOSE(SPLIT(cell, delimiter))

And here is an example of using this formula to split a set of cells from a column into rows:

=transpose(arrayformula(split(A2:A11, " ")))

How to split date and time in Google Sheets

10/18/2024 14:44:39 – this is the date format you usually get when importing data from cloud sources such as Pipedrive or Google Ads. At the same time, for different calculations, you’ll need mostly date (10/18/2024) without time (14:44:39). SPLIT Google Sheets is what you should use to fix that.

Either Split text to columns or the SPLIT function will easily do the job.

Here is what the option 1 looks like:

And here is the SPLIT formula to do the same:

How to split text and cut unnecessary columns in Google Sheets (SPLIT + QUERY)

Let’s say, you have a dataset from which you want to only split text in one column – Customer Name (C2:C). The values from the column are supposed to be divided into separate columns with name and surname. But, some values contain unnecessary data:

If we use a regular SPLIT formula, it will result in an additional column that we don’t need. 

=arrayformula(
   split('All orders'!C2:C, " ")
)

To solve this issue, let’s combine the above formula with the QUERY function, as follows: 

=query(
   arrayformula(
    split('All orders'!C2:C, " ")),
 "select Col1, Col2")

Read our dedicated blog post to learn more about the power of Google Sheets Query Function.

Limitations and errors with splitting cells in Google Sheets

Can I split text into columns without clicks and formulas?

I claimed that Split text to columns is the fastest way to split cells in Google Sheets since it only takes 4 clicks. However, you can execute the split only with 2 clicks. Select a cell or range of cells and use a Google Sheets shortcut – Alt+d,e (or Alt+a,e). Then select a separator. The shortcut will work if Compatible keyboard shortcuts are enabled. 

Date cannot be split

If the formula bar shows date+time 12/31/2024 23:00:00 but you see 12/31/2024 in the cell, the SPLIT function Google Sheets won’t work.

To fix this, you need to apply the date time format to the cell or range of cells. Navigate to Format => Number => Date time or use the shortcut (Alt+o,n,i).

Can’t split certain cells from a column 

If you select not the entire column or cell range, you won’t be able to split the data using Split text to columns. The functionality won’t be available.

So, you’ll need to split them separately using either Split text to columns or the SPLIT function.

We’ve covered the most common cases of using the SPLIT function. Perhaps, yours is much more interesting. If you need a reverse action then read our blog post about merging data in Google Sheets. Good luck with your data!

Zakhar Yung

A content manager at Coupler.io whose key responsibility is to ensure that the readers love our content on the blog. With 5 years of experience as a wordsmith in SaaS, I know how to make texts resonate with readers' queries??