How to Count Comma Separated Values in Excel

Enter the following formula in the result cell. It returns zero for an empty source cell. It also ignores empty items between consecutive commas.

=IF(TRIM(A1)="",0,COUNTA(TEXTSPLIT(A1,",",,TRUE)))

Sources:Microsoft Support: TEXTSPLIT functionMicrosoft Support: COUNTA function

If your version does not have TEXTSPLIT, count the commas and add one. This formula returns zero for an empty cell.

=IF(TRIM(A1)="",0,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)

• red,blue,green returns 3.
• red,,green also returns 3 because the empty section counts.

These formulas treat every comma as a delimiter. They do not parse CSV quotation rules. A comma inside a quoted value can produce an incorrect value count.

Use Excel's CSV import tools when your data has quoted commas, escaped quotation marks, or multiple rows.

Sources:Microsoft Support: Split a cell in Excel