How would you split a date-time value
4/20/2020 8:00:00 into two separate values: date
4/20/2020 and time
8:00:00. The SPLIT function will work well, 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. Go to the Data menu and select Split text to columns.
- Select a separator or let it be detected automatically
The cell value 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 resulting 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.
Check out the options of how to split cells in Excel.
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:
ARRAYFORMULA + SPLIT formula for a column
To apply the SPLIT function 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.
We’ll show you how it works a bit later.
Splitting data in Google Sheets: real-life examples
Let’s check out some practical use of the SPLIT function in real life. For this, we imported some raw data using Coupler.io, a data integration tool to automate data exports from third-party sources, such as Airtable, Xero, Pipedrive, and others into Google Sheets, Excel, or BigQuery. Check out all the available Google Sheets integrations.
Importing data is very simple. You can sign up to Coupler.io using your Google account or install the Coupler.io add-on for Google Sheets from the Google Workspace Marketplace. Then you’ll need to select the source and destination apps for your integration and complete their setup.
If the importer you need is not available, feel free to share your use case with us by filling in this form. This will help us decide which dedicated importer will be created next. You can also try to set up the JSON integration yourselves using your app’s web API.
Each integration allows you to automate data refresh on a custom schedule such as every hour or every 15 minutes. 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 data using the Pipedrive to Google Sheets integration 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 used a data set imported with the Airtable to Google Sheets integration. 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.
Split cells in Google Sheets recap
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. If you need a reverse action then read our blog post about merging data in Google Sheets. Good luck with your data!Back to Blog