# How to count words in a cell in Excel

Count the spaces in the trimmed text and add one: =IF(TRIM(A2)="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1).

**Excel:** `=IF(TRIM(A2)="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)` (Excel 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Text | Words |
| Ergonomic office chair |  |
| Adjustable   standing desk |  |
| Lamp |  |
|  |  |
| Noise-cancelling wireless headset with mic |  |
| The quick brown fox jumps |  |

Formula in B2, filled to B7: `=IF(TRIM(A2)="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)`

Result: 3; 3; 1; 0; 5; 5

## How it works

TRIM leaves exactly one space between words, so the number of words is the number of spaces plus one. `LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))` counts the spaces: the length drops by one for each space removed. The IF returns 0 for an empty cell, which would otherwise count as one word.

## Other ways

- `=IF(TRIM(A2)="",0,COUNTA(TEXTSPLIT(TRIM(A2)," ")))`: Excel 365: split into words and count them.

## Common mistakes

- Without TRIM, extra spaces count as extra words: "  Adjustable   standing desk " would give 9.
- Hyphenated words count as one, and words separated only by a line break are counted as one. Replace CHAR(10) with a space first if your text has line breaks.

## Questions

**How do I count the words in a whole range?**

Sum the per-cell counts: `=SUMPRODUCT(LEN(TRIM(A2:A7))-LEN(SUBSTITUTE(TRIM(A2:A7)," ",""))+(TRIM(A2:A7)<>""))`.

**How do I count how often one word appears?**

Remove it and divide by its length: `=(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),"late","")))/LEN("late")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-words-in-a-cell