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:

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:

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

For example:

**Apples, Bananas, Cheerios**contains**2**commas.**Peaches, Cucumbers**contains**1**comma.**Watermelon**contains**0**commas.

And so on.

**How This Formula Works**

Recall the formula that we used to count the number of commas in cell **B2**:

=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.

**Additional Resources**

The following tutorials explain how to perform other common operations in Excel:

Excel: Count Number of Yes and No Values in Range

Excel: Count Number of Matches Between Two Columns

Excel: How to Count Number of Occurrences