1

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.

2

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.

| improve this answer | |
1

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")
| improve this answer | |
  • 1
    JvdV, nice. I'd never used this combo before, so I learned something. Thanks. – Erik Tyler Jul 17 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.