# Splitting and counting list cells (Excel lesson 22 of 45)

> Break "rush, gift, fragile" into separate cells, and count how many items a cell holds.

Source: https://spreadsheetpractice.com/learn/textsplit-count-clean-lists · Updated October 29, 2026

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Text cleanup"
- **Time:** about 9 minutes, 2 graded exercises
- **Functions:** `TEXTSPLIT`, `COUNTA`, `TRIM`, `SUBSTITUTE`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=TEXTSPLIT(B2,", ")`: Spill the pieces across a row

## How it works

`TEXTSPLIT(text, ", ")` cuts text at every delimiter and spills the pieces across the row. It replaces the old Text to Columns wizard with a formula that updates.

Wrap it to count the pieces: `=COUNTA(TEXTSPLIT(B2,", "))` gives the number of tags in a cell. Without TEXTSPLIT, count the delimiters instead: `=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1`.

If the separators are uneven, such as "a,b, c", split on just the comma and wrap the text in `TRIM` afterwards.

## Syntax

| Formula | What it does |
|---|---|
| `=TEXTSPLIT(B2,", ")` | Spill the pieces across a row |
| `=COUNTA(TEXTSPLIT(B2,", "))` | How many items in the cell |
| `=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1` | The same count in any version |
| `=TRIM(TEXTSPLIT(B2,","))` | Split on commas, then trim spaces |

## Watch out for

- The delimiter must match exactly, including the space: ", " is not ",".
- TEXTSPLIT spills, so the cells to the right must be empty.
- An empty cell counts as one item with the SUBSTITUTE method. Guard it with IF(B2="",0,...).

## The exercises

Solve them in the live grid at [Splitting and counting list cells](https://spreadsheetpractice.com/learn/textsplit-count-clean-lists). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Count the tags.** In C2, count how many tags are in each order's Tags cell (separated by comma and space). Fill down to C6.
2. **Spill the tags.** In E2, split the tags in B2 into separate cells across the row. The tags are separated by a comma and a space.

Previous: [TEXTBEFORE, TEXTAFTER & FIND](https://spreadsheetpractice.com/learn/textbefore-textafter-find) · Next: [DATE, YEAR, MONTH & TEXT](https://spreadsheetpractice.com/learn/date-year-month-text)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
