Count comma-separated labels on a support ticket, and flag the over-tagged ones
LEN doesn't count delimiters on its own, but the length of the whole string minus the length of that same string with every delimiter stripped out, plus one, reconstructs an item count without ever splitting the text apart.
Task:
You track triage for a software company's support desk. Each ticket carries a comma-separated list of labels in column B — some tickets have one label, others have four or more, and there's no fixed count to assume going in. In column C, work out how many labels each ticket has without splitting the text apart. In column D, use IF to flag any ticket with more than 2 labels as "Needs triage" rather than the default "OK" — that many labels usually means more than one team should be looking at it. Fill in rows 2 through 5.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Ticket | Labels | Label count | Status |
| 2 | TKT-101 | billing,urgent | ||
| 3 | TKT-102 | ui,bug,regression,p0 | ||
| 4 | TKT-103 | login | ||
| 5 | TKT-104 | api,timeout,p1 |
What this exercise teachesMay contain the answer
SUBSTITUTE(B2,",","") rebuilds TKT-101's labels without any commas, shortening "billing,urgent" from 14 characters to 13; the one-character difference LEN(B2)-LEN(SUBSTITUTE(B2,",","")) returns is the comma count, not the label count, which is why the formula adds 1 back on — a string with zero commas, like TKT-103's "login", still holds exactly one label. TKT-102's "ui,bug,regression,p0" has three commas, so the same formula gives 3+1 = 4, past the threshold IF(C2>2,...) checks in column D, and it reads "Needs triage"; TKT-104's three labels clear that threshold too, while TKT-101's two and TKT-103's one both read "OK".