Counting multiple occurances within a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to count multiple occurances of a string within a single cell?
As an example, Cell A1 conatins the string "ST,CN,ST" I want to know the number of times "ST" was found in the cell. I also want to continue down the row and continue counting occurances.
 
John Diem said:
Is there a way to count multiple occurances of a string within a
single cell? As an example, Cell A1 conatins the string "ST,CN,ST"
I want to know the number of times "ST" was found in the cell. I
also want to continue down the row and continue counting occurances.
Click to expand...

In general,

=(LEN(string)-LEN(SUBSTITUTE(string,substring,""))/LEN(substring)

To count all instances of "ST" in each cell in B2:D9,

=SUMPRODUCT(LEN(B2:D9)-LEN(SUBSTITUTE(B2:D9,"ST","")))/LEN("ST")
 
Back
Top