Lesson 22 of 45, Text cleanup
Splitting and counting list cells
Quick answer
Break "rush, gift, fragile" into separate cells, and count how many items a cell holds.
- Key formula
=TEXTSPLIT(B2,", ")(Spill the pieces across a row)
Watch it done
2 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
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
=TEXTSPLIT(B2,", ")Spill the pieces across a row=COUNTA(TEXTSPLIT(B2,", "))How many items in the cell=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1The same count in any version=TRIM(TEXTSPLIT(B2,","))Split on commas, then trim spacesWatch 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
- 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.
- 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.
Questions people ask about this
- How to count words in a cell in Excel
- How to count comma-separated items in a cell in Excel
- How to count items in a comma-separated list in Excel
- How to count the number of lines in a cell in Excel
- How to fix spacing in comma-separated lists in Excel
- How to split text into columns with a formula in Excel
Updated Checked by running: every exercise answer on this page was graded by our formula engine on .
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.
- Count the tags
- Spill the tags