Count Commas (,) in Excel Worksheet.

How to count comma or commas (,) in your Microsoft Excel Worksheet with Example. Find out the number of commas in excel cells.




At work, Sometimes we need to count the number of commas in a range of selected cells. However, there is no Excel Function to do this type of task, but here we will know a trick to find out the count of Commas (",") in MS Excel.

As there is no worksheet function that will produce the desired count, here we will use a formula or two. To figure out the number of commas in the range of Excel Worksheet when there could be multiple commas per cell, then you need to use the given below formula:

SYNTAX:-


=LEN(text)-LEN(SUBSTITUTE(text, old_text, new_text, [instance_num))

Note:- Here you can leave instance_num blank as it is optional, so that the SUBSTITUTE Function will work from the starting till the End of the Cell or Text.

EXAMPLE:-

Suppose you have some data in A1 to A10 Cells, that data contains multiple commas (,) in each cell itself. Now you need to count the number of commas (,) present in one cell, then use the above formula in Cell B1 as given below and fill till the end of the Data (B10).

Comma Count

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

If you just want to know the number of cells that have at least one comma in them, the following formula will work just fine:

SYNTAX:-

=COUNTIF(range,"*,*")

EXAMPLE:-

=COUNTIF(A2:A11,"*,*")

There is an alternative to find out the count of commas in your whole worksheet then follow the given below steps, but the below method will give you the count of commas of whole excel sheet or selected range in your Excel file.

    1) Select the range of cells in which you want to count commas.

    2) Press Ctrl+H to display the Replace tab of the Find and Replace dialog box.

    3) In the Find What box, enter a comma.

    4) In the Replace With box, enter a comma.

    5) Click Replace All.

Excel does the replacement and displays a dialog box that shows how many replacements were made.

Display Box