How do I count the number of times string “A” occurs in cell B1

Question

Given the string "AABACCCDAC", how do I count the number of times "C" occurs?

My initial thought was to use countif on a SPLIT of the string. But Split won't split on "" -- a null string.

Answer 1

Sherwood, assuming that the string were in cell A1:

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

In your example string — AABACCCDAC — LEN(A1) would be 10. If we SUBSTITUTE all the occurrences of "C" with null, we'd have the length of "everything else." Subtracting the two tells what's missing... which would be the number of occurrences of "C."

You could also do something similar with REGEXREPLACE:

=LEN(REGEXREPLACE(A1,"[^C]",""))

The [^C] means "everything except 'C'."

I should add that this would only work as stated in your post: with one letter.

If you want to search for longer strings (say "cat"):

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

If you didn't divide by the number of characters in your search string, you'd wind up with the number of characters found instead of the number of strings.

Answer 2

We can still sort of split a string on each position using MID. If we throw in an COUNTIF as per your attempt it could look like:

=ARRAYFORMULA(COUNTIF(MID(A1,SEQUENCE(LEN(A1)),1),"C"))

Though, a bit simpler would be:

=SUMPRODUCT(MID(A1,SEQUENCE(LEN(A1)),1)="c")

Or in case of a longer string (let's stick with the cat =)):

=SUMPRODUCT(MID(A1,SEQUENCE(LEN(A1)),3)="cat")