Title: How to count words in Excel - formula examples
Headings: How to count words in Excel
How to count words in Excel
How to count the total number of words in a cell
How this word counting formula works
Improved formula that properly handles empty cells
How to count specific words in a cell
How this formula counts the occurrences of a specific text in a cell
Case-sensitive formula to count specific words in a cell
Case-insensitive formula to count specific words in a cell
Count the total number of words in a range
Count specific words in a range
=SUMPRODUCT ( (LEN (A2:A4)-LEN (SUBSTITUTE ( (UPPER (A2:A4)),UPPER (C1),"")))/LEN (C1))
You may also be interested in

Content: How this word counting formula works
First, 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: LEN (SUBSTITUTE (A2," ",""))
After 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. Additionally, you use the TRIM function to eliminate extra spaces in the cell, if any. 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. leading and trailing spaces). And all those extra spaces can throw your word count off. 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. Improved formula that properly handles empty cells
The above formula to count words in Excel could be called perfect if not for one drawback - it returns 1 for empty cells. To fix this, you can add an IF statement to check for blank cells: =IF (A2="", 0, LEN (TRIM (A2))-LEN (SUBSTITUTE (A2," ",""))+1)
As you can see in the screenshot above, the formula returns zero for blank cells, and the correct word count for non-empty cells. How to count specific words in a cell
To count how many times a certain word, text, or substring appears in a cell, use the following formula: