Easily count the comma separated values in a single cell in Excel

• Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
• More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
• Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
• Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
• Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
• Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
• More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

This method will introduce a formula to quickly count the total number of all commas or dashes in one cell in Excel. Please do as follows:

Select the cell you will place the counting result, type the formula =LEN(A2)-LEN(SUBSTITUTE(A2,",","")) (A2 is the cell where you will count the commas) into it, and then drag this cell’s AutoFill Handle to the range as you need. See screenshot:

Note: For counting the number of dashes, please replace the commas with the dashes in the formulas as =LEN(A2)-LEN(SUBSTITUTE(A2,"-","")).

Sometimes, you may be not able to remember the formula exactly. Here we recommend the Count times a word appears formula of Kutools for Excel, which can help you quickly count a character or a word in a cell easily. Please do as follows:

1. Select a blank cell you will place the counting result into, and click Kutools > Formulas > Count times a word appears. See screenshot:

2. In the opening Formula Helper dialog box, please specify the cell where you will count the commas/dashes in the Text box, type the “,” or “-” into the Word box, and click the OK button. See screenshot:

And then the total number of commas/dashes in the specified cell has been counted and returned into the selected cell. If necessary, please drag this cell’s AutoFill Handle to the range as you need. See screenshot:

Note: This Count times a word appears formula is also able to count the specified character or word in a range. See below screenshot:

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

Select and count cells if contain certain text sting in Excel

Count specific characters (comma/semicolon/question marks) in cell/range in Excel

Remove all punctuation marks (comma, quotation, apostrophe) from cells