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.
Working with data in Google Sheets often requires splitting text and values from one cell into separate cells. Whether you need to break apart full names, extract specific text, divide columns or rows, or just tidy up your data, splitting cells in Google Sheets is a useful skill to have
In this comprehensive guide, I’ll show you 10 quick and easy ways to split cells in Google Sheets using built-in tools formulas add-ons, and more. With step-by-step instructions and examples, you’ll be able to split cells like a pro in no time!
Why Split Cells in Google Sheets?
Here are some common reasons you may need to split cell contents in Sheets:
- Separate first and last names that are combined in one cell, e.g. “John Doe”
- Split a long URL or file path into multiple columns
- Extract the domain from an email address, e.g. “[email protected]“
- Divide a column of full names into separate first name and last name columns
- Split rows or columns of data to organize it better
- Isolate specific text or numeric values from a larger string
- Split text at certain character positions or around a delimiter
Splitting cells allows you to organize, sort, filter, and analyze your data more effectively. Let’s look at 10 easy ways to do it in Google Sheets.
1. Split Text to Columns Tool
The Split text to columns tool is the standard built-in way to split cells in Google Sheets. Here’s how to use it:
-
Select the cells you want to split.
-
Go to Data > Split text to columns.
-
Choose the delimiter to split by, like comma or space.
-
The text will be split into separate columns.
This is handy for quick splits but has limitations:
- It overwrites your original data
- Can only split by one delimiter at a time
- Doesn’t let you split to rows
2. The SPLIT Formula
The SPLIT formula divides text around a specified delimiter. The syntax is:
=SPLIT(text, delimiter, [split_by_each], [remove_empty])
For example, to split full names by the space character:
=SPLIT(A2," ")
To split an entire column:
=ARRAYFORMULA(SPLIT(A2:A, " "))
Benefits of the SPLIT formula:
- More flexibility than built-in tool
- Split multiple columns at once with ARRAYFORMULA
- Specify multiple delimiters by nesting SPLIT formulas
3. Power Tools Add-on
The Power Tools add-on has several options for splitting text and is more robust than the built-in tools.
To install, go to Add-ons > Get add-ons and search for “Power Tools”.
Once installed, select the cells to split and choose one of these options:
Split Text
Lets you split by multiple delimiters, special characters, and more. Gives you added control like splitting to rows and not overwriting original data.
Split Names
Splits first and last names while handling salutations and suffixes properly.
Split Date & Time
Separates date and time values from a combined cell.
I recommend Power Tools if you need versatility when splitting cells in Google Sheets.
4. Regular Expressions (REGEX)
For advanced text splitting, use regular expressions (REGEX) to look for specific text patterns.
For example:
=REGEXEXTRACT(A2,"(w+)@(w+).com")
This splits an email to isolate the username and domain separately.
REGEX can take some work to learn but gives you precise control to extract text.
5. Text to Columns Formula
The TEXT TO COLUMNS formula is useful for splitting fixed-width text like file paths:
=TEXT(A2,"@@@@@")
The @ symbols specify where to split by character position. This divides a filepath like:
C:UsersJohnDocumentsReport.pdf
Into separate cells like:
C:UsersJohnDocumentsReport.pdf
6. Split Cells by Rows Instead of Columns
The methods above split text into multiple columns. To split into rows instead, use TRANSPOSE:
=TRANSPOSE(SPLIT(A2,","))
This splits the comma-separated values in cell A2 and transposes the results into rows.
7. Use FILTER to Extract Matching Text
To extract specific text from cells, use FILTER with SEARCH or REGEXMATCH to return only matching text.
For example, to extract email domains:
=FILTER(SPLIT(A2,"@"),REGEXMATCH(SPLIT(A2,"@"),".com$"))
This splits on @, then filters to only return values ending in .com.
8. Copy Just Visible Cells with ImportRange
If you split cells across multiple columns, you may want to copy only the visible cells instead of the full rows.
Use ImportRange for this by specifying the visible range:
=IMPORTRANGE("URL","VisibleRange")
9. Use QUERY to Return a Column with SPLIT
The QUERY function lets you modify and transform data. To return just one split column:
=QUERY(SPLIT(A2, ","), "select Col2",0)
This splits column A but only returns the second column.
10. Concatenate and Split Repeatedly
To split on multiple delimiters in one formula, concatenate and split repeatedly:
=SPLIT(SPLIT(CONCATENATE(A2,"|",B2),"|")," ")
This concatenates A2 and B2 with a pipe delimiter, splits by pipe, then splits again by space.
Chaining together SPLIT and other functions lets you make complex splits.
Key Takeaways
-
The Split text to columns tool provides basic splitting but has limitations.
-
Formulas like SPLIT and TEXT TO COLUMNS give you more advanced options.
-
Power Tools is the most versatile add-on for splitting cells.
-
For precision text extraction, use REGEX or filter with SEARCH/REGEXMATCH.
-
Transpose rows and columns with TRANSPOSE after splitting.
-
ImportRange, QUERY, and chaining SPLIT allow more customization.
Learning different ways to split cells in Google Sheets will help you better organize, analyze, and work with your data. Start with the built-in tools, then expand your skills with formulas and add-ons. With a bit of practice, you’ll be able to cleanly split text, numbers, dates, rows, and columns however you need.
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:
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.
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.
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:
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 Cells In Google Sheets
How to split cells into columns in Google Sheets?
One way to split cells into columns in Google Sheets is to use the menu option. Use this option if you don’t want to deal with formulas and don’t expect your data to change in the future. Select the cell you want to split, then go to the Data menu and choose the Split Text To Columns option
How do I split cells with the split function?
Here’s how you can split cells with the SPLIT function. Step 1: Open up your Google spreadsheet file. To begin, we’ll need to open up a spreadsheet file so you can follow the instructions on your own. If you already have an existing document with cells you want to split, then you can use that as well.
How do I use a separator in Google Sheets?
You can pick Tab, Comma, or Custom, or have Google Sheets automatically detect the separator based on the file. If you select Custom, enter the separator that you want to use in the box that displays. The final option is to have text converted to numbers, dates, and formulas. To use this option, simply check the box.