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 Google Sheets 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.
Or watch our new Google Sheets SPLIT Function tutorial on the Coupler.io Academy YouTube channel.
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 Google Sheets.
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.
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.
Google Sheets SPLIT formula syntax
=split(data, delimiter, [split_by_each], [remove_empty_text])
data– Textual or numeric data to splitdelimiter– 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[split_by_each]toFALSE.[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]toFALSE. In this case, you must also specify[split_by_each]parameter (TRUEorFALSE) 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
=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. Here is what we’ve got:
ARRAYFORMULA + SPLIT Google Sheets formula for a column
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.
We’ll show you how it works a bit later.
How to split cells in Google Sheets: real-life examples
Let’s check out some practical use of the SPLIT function Google Sheets in real life. For this, we imported some raw data using Coupler.io, a reporting automation solution that allows you to automate data exports from 50+ business apps, such as Airtable, Xero, Pipedrive, and others. Google Sheets is one of the 6 destinations supported by Coupler.io. In addition to spreadsheets, you can also export data to data warehouses and BI tools. 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 Google Sheets 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 Google Sheets 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!
