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.
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.Sign up to join this community
Sherwood, assuming that the string were in cell A1:
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:
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"):
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.
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:
Though, a bit simpler would be:
Or in case of a longer string (let's stick with the cat =)):