Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Count the Number of Commas/Dashes Separated Values in a Cell in Excel?
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.
6K+ Views
