Spreadsheet Practice

How to split cells into rows in Google Sheets

Google Sheets formula

=TOCOL(ARRAYFORMULA(SPLIT(B2:B5,", ",FALSE)),1)

Split the whole column, then read the pieces into one column without the gaps: =TOCOL(ARRAYFORMULA(SPLIT(B2:B5,", ",FALSE)),1).

Result on the example
a 8-row list that spills from D2 in D2
Excel
See below

See it work

The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.

How it works

ARRAYFORMULA lets SPLIT work on all four cells at once, giving a block of four rows and up to three columns; shorter lists leave empty cells. TOCOL reads that block row by row into a single column, and its second argument, 1, drops the empty cells.

=TOCOL(ARRAYFORMULA(SPLIT(B2:B5,", ",FALSE)),1)

Result{8 rows × 1 col} "budget", "finance", "hiring", "finance", …

  1. B2:B54 rows × 1 col: "budget, finance", "hiring", "finance, payroll, tax", "hiring, onboarding"

    A range: 4 cells in one column.

  2. SPLIT(B2:B5,", ",FALSE){4 rows × 3 cols} "budget", "finance", (empty), "hiring", …

    SPLIT: Splits text across columns at a delimiter.

    • textB2:B54 rows × 1 col: "budget, finance", "hiring", "finance, payroll, tax", "hiring, onboarding"
    • delimiter", "
    • split_by_eachFALSE
  3. ARRAYFORMULA(SPLIT(B2:B5,", ",FALSE)){4 rows × 3 cols} "budget", "finance", (empty), "hiring", …

    ARRAYFORMULA: Runs a formula over whole ranges and returns all the results.

    • array_formulaSPLIT(B2:B5,", ",FALSE){4 rows × 3 cols} "budget", "finance", (empty), "hiring", …
  4. TOCOL(ARRAYFORMULA(SPLIT(B2:B5,", ",FALSE)),1){8 rows × 1 col} "budget", "finance", "hiring", "finance", …

    TOCOL: An array or range as one column, optionally skipping blanks and errors.

    • arrayARRAYFORMULA(SPLIT(B2:B5,", ",FALSE)){4 rows × 3 cols} "budget", "finance", (empty), "hiring", …
    • ignore1

In Excel

In Excel 2024 or Microsoft 365, join the cells and split into rows: =TEXTSPLIT(TEXTJOIN(", ",TRUE,B2:B5),,", ").

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=UNIQUE(TOCOL(ARRAYFORMULA(SPLIT(B2:B5,", ",FALSE)),1))spills 6 × 1Different resultAdd UNIQUE for a list of distinct tags. Each tag once: six tags.
=FLATTEN(ARRAYFORMULA(SPLIT(B2:B5,", ",FALSE)))spills 12 × 1Different resultFLATTEN reads the block the same way but keeps the empty cells. Includes four blank cells.

Common mistakes

  • Without ARRAYFORMULA, SPLIT on a range only splits the first cell.
  • For a single cell, =TRANSPOSE(SPLIT(B4,", ",FALSE)) is simpler.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Split cells into rows

In D2, list every tag from B2:B5 in one column, one tag per row.

Questions

How do I keep the article next to each tag?

Join the article to every piece before splitting, or use a helper column with REGEXREPLACE that prefixes each tag; a pivot-style unpivot needs more steps.

How do I count how often each tag is used?

Put the list in QUERY: =QUERY(TOCOL(ARRAYFORMULA(SPLIT(B2:B5,", ",FALSE)),1),"select Col1, count(Col1) group by Col1",0).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.