In the generic version of the formula, rng represents the range to check, and txt is the word or substring to count.
In the example shown, B5:B8 is the range to check, and C2 contains the text (word or substring) to count.
For each cell in the...Read more
For each cell in the range, SUBSTITUTE removes all the o's from the text, then LEN calculates the length of the text without o's. This number is then subtracted from the length of the text with o's.
Because we are using SUMPRODUCT, the result of all this calculation is a list of items (...Read more
SUMPRODUCT accepts the range B3:B6 as an array of four cells. For each cell in the array, LEN calculates the length of the text as a number. The result is an array that contains 4 numbers. SUMPRODUCT then sums the items in this array and returns the total.Read more
The LEN function is fully automatic. In the example, the formula in the active cell is:
=LEN(B5)
The LEN function simply counts all characters that appear in a cell. All characters are counted, including space characters, as you can see in...Read more
For each cell in the range, SUBSTITUTE removes all spaces from the text, then LEN calculates the length of the text without spaces. This number is then subtracted from the length of the text with spaces, and the number 1 is added to the final result, since the number of words is the number of...Read more
In this example, the goal is to count the total number of words in a cell. Excel doesn't have a dedicated function for counting words. However, with a little ingenuity, you can create a formula to perform this task using a combination of built-in functions. In newer versions of Excel, the best...Read more
