# How to count items in a comma-separated list in Excel

Count the commas and add one: =IF(TRIM(B2)="",0,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1).

**Excel:** `=IF(TRIM(B2)="",0,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1)` (Excel 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Order | Items | Item count |
| A-1001 | chair, desk, lamp |  |
| A-1002 | monitor |  |
| A-1003 |  |  |
| A-1004 | dock, cable, mouse, keyboard |  |
| A-1005 | desk,lamp |  |

Formula in C2, filled to C6: `=IF(TRIM(B2)="",0,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1)`

Result: 3; 1; 0; 4; 2

## How it works

A list of n items has n-1 commas. `LEN(B2)-LEN(SUBSTITUTE(B2,",",""))` counts the commas, and adding one gives the items. The IF returns 0 for an empty cell, which would otherwise count as one item.

## Other ways

- `=IF(TRIM(B2)="",0,COUNTA(TEXTSPLIT(B2,",")))`: Excel 365: split the list and count the pieces.

## Common mistakes

- A trailing comma ("chair, desk,") adds an item that isn't there. In Excel 365, ignore empty pieces: `COUNTA(TEXTSPLIT(B2,",",,TRUE))`.
- Spaces around commas don't matter here, since only the commas are counted.

## Questions

**How do I count the items across all orders?**

Sum the column, or in one formula: `=SUMPRODUCT((TRIM(B2:B6)<>"")*(LEN(B2:B6)-LEN(SUBSTITUTE(B2:B6,",",""))+1))`.

**How do I check whether the list contains a specific item?**

Search with commas around both: `=ISNUMBER(SEARCH(", desk,",", "&B2&","))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-items-in-a-comma-separated-list