Character count of the full text string. This is given by the LEN(Text) part of the formula.Character count of the text string with all the characters we’re trying to count removed. This is given by the LEN(SUBSTITUTE(Text,Character,””)) part of the formula.
In our example LEN(“Apples and bananas”) results in 18 since there are 18 characters in the string. In our example SUBSTITUTE(“Apples and bananas”,”a”,””) results in the text string “Apples nd bnns”, where each occurrence of “a” has been removed. The character count of this new text string is then found by LEN(“Apples nd bnns”) and this results in 14 since there are 14 characters in this string. The difference of LEN(Text)-LEN(SUBSTITUTE(Text,Character,””)) is just the count of all our removed characters from the text string. In our example this is 18-14, so there are 4 “a” characters in the text string!
