Text
Count words in a cell: handle extra spaces and blank cells safely
Count space-separated words after normalizing repeated ordinary spaces, including empty cells and formulas returning empty text.
Why counting raw spaces overcounts messy text
Spreadsheets do not have a built-in WORDCOUNT function. Instead, once TRIM collapses leading, trailing, and repeated internal spaces into single spaces between words, a phrase with N words contains exactly N - 1 spaces.
Comparing LEN(TRIM(text)) against LEN(SUBSTITUTE(TRIM(text), " ", "")) measures how many single spaces separate words; adding 1 converts space count into word count.
| NoteText (A) | Trimmed Length | No-Space Length | Word Count |
|---|---|---|---|
| Ship urgent pallet tomorrow | 27 | 24 | 4 |
| Approved | 8 | 8 | 1 |
| 0 | 0 | 0 |
Guard empty and whitespace-only cells from returning 1
Without an empty check, 0 - 0 + 1 would report 1 word on a blank or space-only cell. Checking OR(COUNTBLANK(A2:A2)=1, LEN(TRIM(A2))=0) returns 0 cleanly before running the space-difference math.
=IF(OR(COUNTBLANK(A2:A2)=1, LEN(TRIM(A2))=0), 0, LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2), " ", "")) + 1)Single-word and multi-word verification
A single word like "Approved" has zero spaces after TRIM (8 - 8 + 1 = 1), while "Net 30 invoice due Friday" has 4 spaces and returns 5 words.
=IF(OR(COUNTBLANK(A3:A3)=1, LEN(TRIM(A3))=0), 0, LEN(TRIM(A3)) - LEN(SUBSTITUTE(TRIM(A3), " ", "")) + 1)Try the example
Use the original values or change them to test your understanding.