Spreadsheet Practice

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)
Pro9 minutes, 2 exercisesTEXTSPLITCOUNTATRIMSUBSTITUTE

Watch it done

2 steps

Watch 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 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

  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.

Questions people ask about this

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.

  1. Count the tags
  2. Spill the tags