How to count words in Excel

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 this formula counts the occurrences of a specific text in a cell

1. The SUBSTITUTE function removes the specified word from the original text.
   In this example, we remove the word input in cell B1 from the original text located in A2:
   SUBSTITUTE(A2, $B$1,"")
2. Then, the LEN function calculates the length of the text string without the specified word.
   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".
3. After that, the above number is subtracted from the total length of the original text string:
   (LEN(A2)-LEN(SUBSTITUTE(A2, $B$1,"")))

   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).

4. Finally, the above number is divided by the length of the word. 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. In this example, 12 is divided by 4 , and we get 3 as the result.

Apart from counting the number of certain words in a cell, you can use this formula to count the occurrences of any text (substring). For example, you can count how many times the text "pick" appears in cell A2:

Case-sensitive formula to count specific words in a cell

As you probably know, Excel SUBSTITUTE is a case-sensitive function, and therefore the word counting formula based on SUBSTITUTE is case-sensitive by default:

Case-insensitive formula to count specific words in a cell

If you need to count both uppercase and lowercase occurrences of a given word, use either the UPPER or LOWER function inside SUBSTITUTE to convert the original text and the text you want to count to the same case.

Or

For example, to count the number of occurrences of the word in B1 within cell A2 ignoring case, use this formula:

=(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($B$1),"")))/LEN($B$1)

As demonstrated in the below screenshot, the formula returns the same word count regardless of whether the word is typed in UPPERCASE (cell B1), lowercase (cell D1) or Sentence case (cell C1):