Text
Count Words in Cell
Count the total number of words in a cell by counting spaces and adding one. Useful for content length validation, text analysis, and data quality checks.
Excel Formula
=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1Step-by-Step Explanation
1
TRIM removes extra spaces from the text
2
LEN(TRIM(A2)) gets the total character count
3
SUBSTITUTE removes all spaces
4
The difference is the number of spaces
5
Adding 1 gives the word count (words = spaces + 1)
Example
| Text (A) | Word Count (B) |
|---|---|
| Hello World | 2 |
| The quick brown fox jumps | 5 |
| One | 1 |
Result: =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1 → 2
Common Variations
Handle empty cells
=IF(A2="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)Returns 0 for empty cells
Character count
=LEN(A2)Total characters including spaces
Character count no spaces
=LEN(SUBSTITUTE(A2," ",""))Characters without spaces
Need a Custom Version?
Use our AI generator to create a formula tailored to your specific data and requirements.
Try It with AI →