Table of Contents

You can use the following formula to count the number of commas in a cell in Excel:

=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))

This particular formula counts the number of commas in cell B2.

The following example shows how to use this formula in practice.

Example: How to Count Number of Commas in Cell in Excel

Suppose we have the following dataset that shows the grocery list of various people:

countcomma1

Suppose we would like to count the number of commas in each cell in column B.

We can type the following formula into cell C2 to do so:

=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))

We can then click and drag this formula down to each remaining cell in column C:

Excel count number of commas in cell

Column C now displays the number of commas in each corresponding cell in column B.

For example:

And so on.

How This Formula Works

=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))

Here is how this formula works:

First, we use LEN(B2) to get the length of the entire string in cell B2.

This returns 25.

Then we use SUBSTITUTE(B2, “,”,””) to substitute all commas in the string with nothing. This removes all commas.

Then we use the LEN function to get the length of the string in cell B2 with all commas removed.

This returns 23.

Lastly, we subtract the length of the string without commas from the length of the entire string to get 25 – 23 = 2.

This tells us that cell B2 contains 2 commas.

We repeat this same process for each cell in column B.