Count Words in Excel

Unlike in Microsoft Word, where we can easily count the number of words. Microsoft Excel does not have a built-in word count function. However, we have formula functions that support this function. 
We will guide you.
Counting words in Excel is very simple and easy. We use Excel functions like TRIM, LEN, and SUBSTITUTE.

Trim: This function removes duplicate or extra spaces from a cell or a cell range. The spaces could even be found before or after the word in a cell.
Len: This function returns the length of a string in a cell or a cell range. It includes spaces as well.
Substitute: This function replaces anything in a cell by any text, number etc., of our choice. In our case, this function will replace spaces with blanks.

Count the total number of words in a cell. 

To count the number of words in a cell, we will use the following syntax:

=LEN(TRIM(cell))-LEN(SUBSTITUTE(cell," ",""))+1

First we will use the TRIM function to remove extra spaces.
There is a text string in cell A2, and we want to count the total number of words. So it will be shown in cell B2.

Press Enter. Now we will calculate the length of the string, as shown below:

Press Enter to get the length of the string without spaces.

Now, we will use a SUBSTITUTE function to replace the old text with the new in the string. Here we will replace the spaces " " with blanks. "

Press Enter, and now we see the string without any spaces.
Now to get the length of the string without spaces, we will use the LEN function as shown below:

Finally, to get the count of words in the string, we will subtract the length of the string with spaces (40) with the length of the string without spaces (35) and by adding 1 to it. Adding 1 will give us the total number of words, as spaces are one less than words.

Press Enter, and you will see the exact count of words in the string in cell A2.

Things to Remember About Count Word in Excel

As a practice, use the TRIM function in writing the formula as it will eliminate the chances of error if the cell or range has extra spaces.
Whenever you want to know the count of a specific text in a cell or range, always write that word in double-quotes in the formula like “word”.
SUBSTITUTE is case sensitive, so be careful in writing the word that you want to substitute. It should be in the same case as it is in the original text, else it won’t be substituted.
Always press Ctrl+Shift+Enter to calculate the array SUM formula.