# How to split cells into rows in Google Sheets

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

**Google Sheets:** `=TOCOL(ARRAYFORMULA(SPLIT(B2:B5,", ",FALSE)),1)`

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

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Article | Tags |  | All tags |
| Q3 budget review | budget, finance |  |  |
| New hire guide | hiring |  |  |
| Payroll changes | finance, payroll, tax |  |  |
| Onboarding checklist | hiring, onboarding |  |  |

Formula in D2: `=TOCOL(ARRAYFORMULA(SPLIT(B2:B5,", ",FALSE)),1)`

Result (spills 8 × 1): budget; finance; hiring; finance; payroll; tax; hiring; onboarding

## 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.

## Other ways

- `=UNIQUE(TOCOL(ARRAYFORMULA(SPLIT(B2:B5,", ",FALSE)),1))`: Add UNIQUE for a list of distinct tags. (Each tag once: six tags.)
- `=FLATTEN(ARRAYFORMULA(SPLIT(B2:B5,", ",FALSE)))`: FLATTEN 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/split-cells-into-rows