#Word 2016 word count not updating how to
#Word 2016 word count not updating plus

In other words, you divide the number of characters contained in all occurrences of the target word by the number of characters contained in a single occurrence of that word. Finally, the above number is divided by the length of the word.

The result of this operation is the number of characters contained in all occurrences of the target word, which is 12 in this example (3 occurrences of the word ' moon', 4 characters each).

After that, the above number is subtracted from the total length of the original text string:.

In this example, LEN(SUBSTITUTE(A2, $B$1,'')) returns the length of the text in cell A2 after removing all characters contained in all occurrences of the word ' moon'.

Then, the LEN function calculates the length of the text string without the specified word.

In this example, we remove the word input in cell B1 from the original text located in A2:

The SUBSTITUTE function removes the specified word from the original text.

How this formula counts the occurrences of a specific text in a cell If you plan on copying your formula to multiple cells, be sure to fix the reference to the cell containing the word to count with the $ sign. As a result, you will get a more versatile formula to count words in Excel. Instead of entering the word to be counted directly in the formula, you can type it in some cell, and reference that cell in your formula. =(LEN( cell)-LEN(SUBSTITUTE( cell, word,'')))/LEN( word)įor example, let's calculate the number of ' moon' occurrences in cell A2: To count how many times a certain word, text, or substring appears in a cell, use the following formula: =IF(A2='', 0, LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,' ',''))+1)Īs you can see in the screenshot above, the formula returns zero for blank cells, and the correct word count for non-empty cells.

To fix this, you can add an IF statement to check for blank cells: The above formula to count words in Excel could be called perfect if not for one drawback - it returns 1 for empty cells. Improved formula that properly handles empty cells To guard against this, before calculating the total length of the string, we use the TRIM function to remove all excess spaces except for single spaces between words. And all those extra spaces can throw your word count off. Sometimes a worksheet may contain a lot of invisible spaces, for example two or more spaces between words, or space characters accidentally typed at the beginning or end of the text (i.e.

#Word 2016 word count not updating plus

=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,' ',''))+1Īnd then, you can copy the formula down to count words in other cells of column A:įirst, you use the SUBSTITUTE function to remove all spaces in the cell by replacing them with an empty text string ('') for the LEN function to return the length of the string without spaces:Īfter that, you subtract the string length without spaces from the total length of the string, and add 1 to the final word count, since the number of words in a cell equals to the number of spaces plus 1.Īdditionally, you use the TRIM function to eliminate extra spaces in the cell, if any. Where cell is the address of the cell where you want to count words.įor example, to count words in cell A2, use this formula: To count words in a cell, use the following combination of LEN, SUBSTITUTE and TRIM functions:

#Word 2016 word count not updating how to

How to count the total number of words in a cell

Count only specific words / text in a range.

Count the total number of words in a range.

And we will be using this approach to count words in Excel. Luckily, by combining serval functions you can make more complex formulas to accomplish almost any task. Unfortunately, Excel doesn't provide any built-in tool for counting the number of words.

Microsoft Excel has a handful of useful functions that can count nearly everything: the COUNT function to count cells with numbers, COUNTA to count non-blank cells, COUNTIF and COUNTIFS to conditionally count cells, and LEN to calculate the length of a text string. The tutorial explains how to count words in Excel by using the LEN function in combination with other Excel functions, and provides case-sensitive and case-insensitive formulas to count total or specific words/text in a cell or range.