Google Sheets – Count Occurrences of a String in a Cell

formulasgoogle sheets

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.

Best Answer

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.

Related Topic