How To Split Cells In Google Sheets

You can either:
- Select your data and go to ➜ in the main menu, or
- Use the SPLIT function to split:
- A cell: =SPLIT(A1," ")
- A column: =ArrayFormula(SPLIT(A1:A," "))
- With multiple delimiters: =SPLIT(A1,",;",TRUE)
- Vertically: =TRANSPOSE(SPLIT(A1," "))
- With fixed width (2): =ArrayFormula(ARRAY_CONSTRAIN(SPLIT(A1:A," "),ROWS(SPLIT(A1:A," ")),2))
Keep reading for detailed descriptions of everything here.
There are two ways to split your data into columns in Google Sheets, the:
- Built-in [quick and easy] feature
- SPLIT function [more flexible: multiple delimiters, vertical split, fixed columns]
Not on desktop? For mobile devices using the Google Sheets iPhone/iPad and Android apps you'll need to use the SPLIT function method.
If instead of splitting data into multiple cells you want to 'split' merged cells, you want to unmerge cells.
How To Split Text To Columns In Google Sheets
The quick and easy
cell splitting feature is perfect if:- Your data has a single, consistent delimiter/separator
- You're happy for the source data to be altered
- You want your data split horizontally
Delimiters (or separators) are characters that indicate the beginning and/or end of a piece of data.
For a full name (Kieran Dixon) the delimiter is a space character (" ") that indicates the end of the first name and the beginning of the last name.
STEP 1: Select a cell, range, or column in your sheet:

You can't select a row, a range where the split data would overlap, or make multiple selections.
STEP 2: In the main menu go to ➜ :

As soon as you click the
option your selection will split into the columns to the right of your selection and overwrite any nearby data as it expands.STEP 3: Now that your selection is split, you can choose a specific delimiter using the Separator: menu or define a delimiter by typing it in:

Here's the whole process in action:

When you paste data you have the option to split it into columns.
After pasting, click on the paste icon that appears and in the dropdown choose the
option:
This will work with a single cell, a range or even a whole column.


FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:

You'll get updates from me with an easy-to-find "unsubscribe" link.
Split Cells Using SPLIT In Google Sheets
Splitting data horizontally into cells using the SPLIT function gives you greater flexibility and allows you to leave your source data unchanged.
Here's the function syntax:
- text = the text you want split
- delimiter = the separator you want the text to split around
- [split_by_each] = optional setting as to whether you want the text to split around each character of the delimiter (TRUE - default) or the entire delimiter (FALSE)
- [remove_empty_text] = optional setting as to whether empty text output from the split (where two delimiters occur consecutively in the original text) is removed (TRUE - default) or included (FALSE)
The [split_by_each] option allows you to split using multiple delimiters.
If your text contains both commas and semicolons, you can use this function to split it with a single formula:
Time to work through an example.
You have a list of names and need to split the full names into the first and last names:
A | B | C | D | |
1 | Full name | Formula | Output | |
2 | Michael Scott | =SPLIT(A2," ") | Michael | Scott |
3 | Dwight Schrute | =SPLIT(A3," ") | Dwight | Schrute |
4 | Jim Halpert | =SPLIT(A4," ") | Jim | Halpert |
5 | Pam Beesly | =SPLIT(A5," ") | Pam | Beesly |
6 | Andy Bernard | =SPLIT(A6," ") | Andy | Bernard |
7 | Stanley Hudson | =SPLIT(A7," ") | Stanley | Hudson |
8 | Phyllis Vance | =SPLIT(A8," ") | Phyllis | Vance |
The SPLIT function in its simplest form does all of the work using a space (" ") as the delimiter.
By slightly modifying this formula, you can split an entire column:
Split A Column In Google Sheets
It's far more convenient to have a single formula than to have a formula in each row of your data.
By combining the SPLIT function with the ArrayFormula function, you can force SPLIT to accept an array of text values instead of just one.
As such, it will output multiple text values:
A | B | C | D | |
1 | Full name | Formula | Output | |
2 | Michael Scott | =ArrayFormula(SPLIT(A2:A8," ")) | Michael | Scott |
3 | Dwight Schrute | Dwight | Schrute | |
4 | Jim Halpert | Jim | Halpert | |
5 | Pam Beesly | Pam | Beesly | |
6 | Andy Bernard | Andy | Bernard | |
7 | Stanley Hudson | Stanley | Hudson | |
8 | Phyllis Vance | Phyllis | Vance |
Split Cells Vertically In Google Sheets
By default the SPLIT function split data into columns.
feature andBut sometimes you want to split text into rows.
You can achieve this by wrapping your SPLIT function in the TRANSPOSE function:
A | B | C | |
1 | Text | Formula | Output |
2 | 1,2,3,4,5 | =TRANSPOSE(SPLIT(A2,",")) | 1 |
3 | 2 | ||
4 | 3 | ||
5 | 4 | ||
6 | 5 |
Split Data To Columns Of Fixed Width In Google Sheets
When you split a column of data the output width can vary.
Here's an example:
A | B | C | D | E | |
1 | Full name | Formula | Output | ||
2 | Walter Bernard Sr | =ArrayFormula(SPLIT(A2:A5," ")) | Walter | Bernard | Sr |
3 | Ellen Bernard | Ellen | Bernard | ||
4 | Andy Bernard | Andy | Bernard | ||
5 | Walter Bernard Jr | Walter | Bernard | Jr |
This can throw your data out of whack so it'd be handy if your output could be limited to a fixed width.
This is possible using the ARRAY_CONSTRAIN function:
- input_range = the array of values to get a sample from
- num_rows = how many rows to sample
- num_cols = how many columns to sample
For this example:
- input_range = output of the SPLIT function
- num_rows = the number of rows output by the SPLIT function (you can use the appropriately named ROWS function for this)
- num_cols = the fixed width you want
Here's a formula that does just that:
A | B | C | D | E | |
1 | Full name | Formula | Output | ||
2 | Walter Bernard Sr | =ArrayFormula(ARRAY_CONSTRAIN(SPLIT(A2:A5," "),ROWS(SPLIT(A2:A5," ")),2)) | Walter | Bernard | |
3 | Ellen Bernard | Ellen | Bernard | ||
4 | Andy Bernard | Andy | Bernard | ||
5 | Walter Bernard Jr | Walter | Bernard |


FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:

You'll get updates from me with an easy-to-find "unsubscribe" link.
