How to count the number of words in a cell
✓ Verified in LibreOffice 25.8.7.3Count how many words are in a text string, ignoring extra spaces.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1 | Word count = number of spaces + 1, after trimming. |
| Google Sheets | =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1 | Identical. |
| LibreOffice Calc | =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1 | Identical. |
How it works
TRIM removes extra spaces. The word count equals the number of single spaces between words plus one. SUBSTITUTE(...," ","") deletes the spaces, and the drop in length tells you how many there were. 'the quick brown fox' has 3 spaces, so 4 words. (An empty cell would wrongly return 1; wrap in IF(A2="",0,...) if that matters.)
Verified, not just documented
We ran =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1 in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 4 — exactly the expected result. Every formula here is confirmed by actually executing it.