Count the number of words in a text cell by counting spaces.
Copy this formula
=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2, " ", ""))+1Words equal spaces plus one. TRIM removes extra spaces first, then subtracting the no-space length from the trimmed length gives the space count; adding one yields the word count.
=LEN(TRIM("the quick fox"))-LEN(SUBSTITUTE("the quick fox", " ", ""))+1→3Three words.
Return 0 for blanks
=IF(TRIM(A2)="", 0, LEN(TRIM(A2))-LEN(SUBSTITUTE(A2, " ", ""))+1)Avoids counting an empty cell as 1.
Zero spaces plus one is one. Use the IF variant above to return 0 for blanks.
Need a formula for your own data?
Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.