Excel + Google Sheets · AI prompt
AI wrote this formula for me
Total characters minus characters without spaces = number of spaces. Plus one = words.
The formula
=LEN(TRIM(A2)) - LEN(SUBSTITUTE(A2, " ", "")) + 1What each part means
LEN(TRIM(A2))All charactersLEN(SUBSTITUTE(...))Without spaces+ 1Spaces + 1 = words
Example
| A | B | |
|---|---|---|
| 1 | Text | Words |
| 2 | Thank you for your order | |
| 3 | Send the report by Friday | |
| 4 | Meeting moved to 3pm |
| A | B | |
|---|---|---|
| 1 | Text | Words |
| 2 | Thank you for your order | 5 |
| 3 | Send the report by Friday | 5 |
| 4 | Meeting moved to 3pm | 4 |
Step by step
- Open ChatGPT, Gemini or Copilot.
- Describe your columns and what you want, in your own words.
- Paste the formula it gives you in cell B2:
=LEN(TRIM(A2)) - LEN(SUBSTITUTE(A2, " ", "")) + 1 - Press Enter and drag it down.
Where it works
- Every version of Excel
- Google Sheets
- Excel for the web (free)
The same formula in other languages
- English AI prompt
- Español Prompt de IA
- Português Prompt de IA