Count Commas and Dashes in Excel Cell

Microsoft Excel is a strong application with a variety of functions for analysing and manipulating data. The need to count the number of values within a cell that are separated by commas or dashes is a frequent task. Knowing how to count these values quickly might save you time and effort when working with enormous datasets or just organising information.

In this lesson, we'll show you how to use Excel's built-in functions and formulae to count the number of commas or dashes that separate the numbers in a cell. You will have a thorough understanding of the procedures involved by the end of this tutorial and be able to complete this activity with ease.

Count the Number of Commas/Dashes Separated Values in a Cell

Here we will first get any one of the values using the formula, then use the autofill handle to complete the task. So let us see a simple process to know how you can count the number of commas or dashes separated values in a cell in Excel.

Consider an Excel sheet where you have a list of cells with comma-separated values, similar to the below image.

First, to count the number of comma-separated values, click on an empty cell and enter the formula as =LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1 and drag down using the autofill handle.

Empty cell > Formula > Enter.

Note

If you want to count the number of dashed-separated values, replace the comma with a dash in the formula =LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1 and drag down using the autofill handle.

This is how you can count the number of commas or dashes between values in Excel.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can count the number of commas or dashes separated values in a cell in Excel to highlight a particular set of data.