In one of our blog posts, we connected a weekly schedule template to Airtable for automatic data import. One of the tasks within was to split a date-time (
4/20/2020 8:00:00) into two separate ones: for date (
4/20/2020) and for time (
8:00:00). The SPLIT function did the job for us. However, it’s not the only option to use. Read on to learn more about how you can split cells in Google Sheets.
Split text to columns: a shortcut
The fastest way to divide data in a cell or multiple cells is to press Alt+a,e. It will work if Compatible keyboard shortcuts are enabled. For more on this, read Google Sheets Shortcuts in Action.
With a mouse or touchpad, you’ll have to do the following:
- Select a cell or cells with the data to be split
- Open the Data menu and select Split text to columns
- Once you pick a Separator, the data will be split into fragments
Do the same if you need to split data in multiple cells:
This is the fastest way to split Google Sheets. However, it affects the source data. If you need to leave the source data unchanged and customize the data split, use the SPLIT function.
SPLIT function 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 resulted from the split.
A delimiter is a character or symbol, around which the data will be split into fragments. It may be any textual, numeric or punctuation symbol.
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.
[split_by_each]– An optional parameter to change the splitting behavior of the delimiter. By default, 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
[remove_empty_text]– An optional parameter, which removes empty text cells from the split fragments. If you want to turn it off, set
FALSE. In this case, you must also specify
FALSE) in your SPLIT formula.
SPLIT formula example
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. Here is what we’ve got:
SPLIT formula for a column
To apply the SPLIT function to entire column, you’ll need to combine it with ARRAYFORMULA as follows:
=arrayformula( split(data, delimiter, [split_by_each], [remove_empty_text]) )
We’ll show you how it works a bit later.
Splitting data in Google Sheets: real life examples
Let’s check out some practical uses of the SPLIT function in real life. For this, we’ll need raw data. Coupler.io can help with that, since it’s a tool to pull data from third-party sources, such as Airtable, Xero, Pipedrive and others into Google Sheets. Check out the available integrations.
Importing data is very simple. First, install Coupler.io:
- Use this direct link to install the add-on from the G Suite Marketplace
- You can install it right from your spreadsheet (Add-ons => Get add-ons)
Then you’ll need to set up an importer, which will connect your spreadsheet to a specific app or data source. If you need to pull data from Airtable, pick the Airtable importer; in the case of Xero, go with the Xero importer and so on.
If the importer you need is not available, feel free to share your use case with us by filling out this form. This will help us decide which dedicated importer will be created next. You can also try to set up the integration yourselves using the JSON client importer. For more on this, read How to Import JSON to Google Sheets Without Coding.
During the importer setup, don’t forget to enable Automatic data refresh. This feature will let you forget about manual data import – Coupler.io will do this for you at the set frequency.
How to split date and time in Google Sheets
10/18/2019 14:44:39 – This is the date format you usually get when importing data from Airtable, Pipedrive and other sources. At the same time, for different calculations, you’ll need either date (
10/18/2019) or time (
14:44:39). SPLIT is what you should use to fix that.
We’ve imported Deals from Pipedrive and needed to split the add_time column (AH2:AH). Here is the formula and how it works:
=arrayformula( split('Pipedrive Deals'!AH2:AH," ") )
Note: If you see
2019-06-30 in the cell, whereas the formula bar shows
6/30/2019 22:00:00, the SPLIT function won’t work until you apply the date time format (Alt+o,n,i) to the cell/cells:
How to split text in Google Sheets (SPLIT + QUERY)
For this use case, we’ve imported Airtable data. Our goal is to split the Customer Name column (C2:C) into separate columns with name and surname. However, some values in our database 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.
To wrap up
We’ve covered the most common cases of using the SPLIT function. Perhaps, yours is much more interesting. Use the comments section below to share it with us, and we will be happy to include it in this copy. The Coupler.io team aims to make things better and we encourage you to join us! Good luck with your data!Back to Blog