Google Sheets Cheat Sheet

How To Split Cells In Google Sheets

You can either:

  • Select your data and go to DataSplit text to columns 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:

  1. Built-in Split text to columns feature [quick and easy]
  2. 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 Split text to columns cell splitting feature is perfect if:

  1. Your data has a single, consistent delimiter/separator
  2. You're happy for the source data to be altered
  3. 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:

shows how to select data before using the split text to columns feature of google sheets

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 DataSplit text to columns:

shows the location of the split text to columns feature of google sheets within the data dropdown of the main menu

As soon as you click the Split text to columns 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 Custom delimiter by typing it in:

shows how to change the separator (or delimiter) when using the split text to columns feature of google sheets

Here's the whole process in action:

shows the whole process of using the split text to columns feature of google sheets

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 Split text to columns option:

shows how to split text to columns when pasting data in google sheets

This will work with a single cell, a range or even a whole column.

hand pointing emoji hand pointing emoji

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Google Sheets Cheat Sheet

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:

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
  • 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:

=SPLIT(A1,",;",TRUE)

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:

ABCD
1Full nameFormulaOutput
2Michael Scott=SPLIT(A2," ")MichaelScott
3Dwight Schrute=SPLIT(A3," ")DwightSchrute
4Jim Halpert=SPLIT(A4," ")JimHalpert
5Pam Beesly=SPLIT(A5," ")PamBeesly
6Andy Bernard=SPLIT(A6," ")AndyBernard
7Stanley Hudson=SPLIT(A7," ")StanleyHudson
8Phyllis Vance=SPLIT(A8," ")PhyllisVance

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:

ABCD
1Full nameFormulaOutput
2Michael Scott=ArrayFormula(SPLIT(A2:A8," "))MichaelScott
3Dwight SchruteDwightSchrute
4Jim HalpertJimHalpert
5Pam BeeslyPamBeesly
6Andy BernardAndyBernard
7Stanley HudsonStanleyHudson
8Phyllis VancePhyllisVance

Split Cells Vertically In Google Sheets

By default the Split text to columns feature and SPLIT function split data into columns.

But sometimes you want to split text into rows.

You can achieve this by wrapping your SPLIT function in the TRANSPOSE function:

ABC
1TextFormulaOutput
21,2,3,4,5=TRANSPOSE(SPLIT(A2,","))1
32
43
54
65

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:

ABCDE
1Full nameFormulaOutput
2Walter Bernard Sr=ArrayFormula(SPLIT(A2:A5," "))WalterBernardSr
3Ellen BernardEllenBernard
4Andy BernardAndyBernard
5Walter Bernard JrWalterBernardJr

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:

=ARRAY_CONSTRAIN(input_range, num_rows, num_cols)
  • 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:

ABCDE
1Full nameFormulaOutput
2Walter Bernard Sr=ArrayFormula(ARRAY_CONSTRAIN(SPLIT(A2:A5," "),ROWS(SPLIT(A2:A5," ")),2))WalterBernard
3Ellen BernardEllenBernard
4Andy BernardAndyBernard
5Walter Bernard JrWalterBernard

hand pointing emoji hand pointing emoji

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Google Sheets Cheat Sheet

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

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.

Want Better-Looking Google Sheets?

Google Sheets Cheat Sheet

Get my 12-tip cheat sheet that will make your spreadsheets more user-friendly.

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

🗙