SUBSTITUTE removes the substring from the original text and LEN calculates the length of the text without the substring.
This number is then subtracted from the length of the original text.
The result is the number of characters that were removed by SUBSTITUTE.
Finally, the number of characters removed is divided by the length of the substring.
Handling case
SUBSTITUTE is a case-sensitive function, so it will match case when running a substitution.
Normalizing text
Counting words in Excel is tricky because Excel doesn’t support regular expressions.
find “fox " but not “foxes”).
Punctuation and case variations make this problem quite challenging.
One workaround is to useanother formulain ahelper columnto “normalize text” as a first step.
you could look for " fox " in the normalized text.
LEN will also count characters in numbers, but number formatting is not included.
SUBSTITUTE Function
The Excel SUBSTITUTE function replaces text in a given string by matching.
Numbers, punctuation, and spaces are not affected.
