Counting multiple occurances within a cell

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.
 
H

Harlan Grove

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")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count the number of occurances in a cell 2
Counting occurances based on multiple variables 4
simple counting gone awry 3
Counting # of occurances of words in text 11
SUMPRODUCT HELP 1
Formula Help 9
Counting the number of times a character occurs in a cell 1
Conditional Counting 7

Top