How to count words in a cell in Excel

The word count formula

Excel does not have a word count function or a word count in the status bar, so you count the gaps between words instead. TRIM collapses runs of spaces to single spaces and strips the ends. LEN of that, minus the length with every space removed, gives the number of spaces between words. Add 1, because three words have two gaps.

Note: the second LEN can use the original cell. Removing all spaces gives the same length either way.

Why the TRIM matters

" Sent quote " has two words but six spaces. Without TRIM, the formula would count six gaps and say 7 words. With TRIM, the text becomes "Sent quote" first, which has one gap.

Data pasted from emails, web forms and CRMs is full of doubled and trailing spaces, so always keep the TRIM in.

Return 0 for empty cells

On an empty cell the basic formula still adds 1 and returns 1 word. Check for empty text first. Using LEN(TRIM()) also catches cells that hold nothing but spaces.

Count words in a whole column

SUMPRODUCT runs the formula on every cell and adds the results. Multiplying by (LEN(TRIM(A2:A7))>0) turns empty cells into 0 instead of 1, so a blank row does not add a phantom word.

Count how many times a specific word appears

To count "call", remove it and see how many characters disappeared, then divide by the length of the word. UPPER on both sides makes it ignore case, so "Call" and "call" both count.

This counts the letters anywhere, so "call" inside "call-back" counts too, and so would "recall". If that matters, search for " call " with spaces and add a space to both ends of the text: " "&UPPER(A7)&" ".

Excel 365: TEXTSPLIT

In Excel for Microsoft 365, =COUNTA(TEXTSPLIT(TRIM(A2)," ")) splits the note into one cell per word and counts them. It reads more clearly, and you can pass several delimiters, such as {" ",","}, to also split on commas. It does not handle empty cells on its own, so keep the IF check. The LEN formula above works in every version.