Find and count instances of a character in a string in Excel

I want to count the number of commas in a cell, if any.

instr("A,B,C,D",",")

tells me the location of the first comma (2), but I want the total number of commas to be returned (7). I've been using a laborious character-by-character check from 1 to len(string) and incrementing the variable CommaCount every time it finds a comma, but I'm pretty sure there's an easier way.

Answer

try the forllowing formula

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

(for formulas, depending on your country, you might have to change ; with , or the opposite

Other excel answers

 

 

You can find similar Excel Questions and Answer hereunder

1) How can I identify the cells that influence a particular cell to help with debugging of my spreadSheet or understand a spreadSheet that I inherited?

2) Can I determine the number of times a character appears in a cell using formula?

3) How can I identify all cells with Data Validation in my WorkSheet?

4) Split string into multiple columns in Excel

5) How to modify strings in VBA, what are the functions you can manipulate strings with

6) How do I use Find to determine last occurrence of a string in a WorkSheet range using VBA?

7) I have a complex WorkSheet with lot of fields and data - How can I quickly find the cells that have formulas?

8) How can I find the count of records that meet multiple conditions in my raw data table?

9) How can I get the count of number of series in a Chart using VBA?

10) I am a construction Engineer. Considering a room with dimension, 14ft x 12ft can I find the side of the largest square tile which can tile the room?

 

Here the previous and next chapter