How to count items in a comma-separated cell
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)How many tags, emails, or entries live in one delimited cell.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1 | Counts separators and adds one. 365 alternative: =COLUMNS(TEXTSPLIT(A2,",")). |
| Google Sheets | =LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1 | Identical; or =COUNTA(SPLIT(A2,",")). |
| LibreOffice Calc | =LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1 | Identical. |
How it works
Removing the commas shortens the text by exactly the number of separators; items are always one more than that: "red,green,blue" loses 2 characters → 3 items. It's the same LEN/SUBSTITUTE trick as word counting, pointed at a different delimiter. Guard the empty-cell case with IF(A2="",0,...) — an empty cell would otherwise count as 1 — and TRIM first if the data has stray spaces around separators you also want to count on.
Verified, not just documented
We ran =LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1 in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 3 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 3 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
LEN · SUBSTITUTE — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to count occurrences of a substring in a cell
- How to count the total words in a range
- How to count the number of words in a cell
- How to capitalize only the first letter (sentence case)
- How to convert a column number to a letter