Count the words in a cell
PremiumIn B2, count the words in A2. There is one more word than there are spaces, and LEN(A2) minus the length without spaces, LEN(SUBSTITUTE(A2," ","")), is the number of spaces.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Headline | Words | ||||
| 2 | Fast free shipping on every order | |||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock FIND and SEARCH and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Next with premium:Joining text with & and TEXTJOINSUBSTITUTE, TEXTBEFORE and TEXTAFTER
Need help?
Why this works
The headline is 33 characters long and 28 without its spaces, so it has 33 - 28 = 5 spaces. Words sit between the spaces, so there is one more of them than there are spaces: 5 + 1 = 6.
Common mistake: Forgetting about extra spaces. Two spaces in a row count as an extra word, so wrap A2 in TRIM when the text was typed by hand.
The answer
- B2
=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1
Count the spaces and add 1, =LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1. Wrap A2 in TRIM if the text may have extra spaces.