# How to count occurrences of a word in a cell in Excel

Remove the word, compare lengths and divide by the word's length: =(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($E$1),"")))/LEN($E$1).

**Excel:** `=(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($E$1),"")))/LEN($E$1)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Review | Mentions |  | Word | late |
| Delivery was late, then late again |  |  |  |  |
| Arrived on time |  |  |  |  |
| Late order, late refund, late reply |  |  |  |  |
| Not too late |  |  |  |  |
| Great product |  |  |  |  |
| latest model, not late |  |  |  |  |

Formula in B2, filled to B7: `=(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($E$1),"")))/LEN($E$1)`

Result: 2; 0; 3; 1; 0; 2

## How it works

Removing every "late" shortens the text by 4 characters per match, so the length difference divided by `LEN($E$1)` is the number of matches. LOWER on both sides makes "Late" count too.

The last row shows the limit: "latest" contains "late", so it counts.

## Other ways

- `=(LEN(A2)-LEN(SUBSTITUTE(A2,$E$1,"")))/LEN($E$1)`: Case-sensitive version. ("Late order, late refund, late reply" gives 2, because "Late" with a capital is skipped.)

## Common mistakes

- Without dividing by the word's length you get the number of characters removed (8 instead of 2).
- Parts of longer words match too: "late" is found in "latest" and "chocolate". To count whole words only, pad with spaces and remove punctuation first: `" "&SUBSTITUTE(LOWER(A2),","," ")&" "` and search for `" late "`.

## Questions

**How do I count the word across a whole column?**

Wrap it in SUMPRODUCT: `=SUMPRODUCT((LEN(A2:A7)-LEN(SUBSTITUTE(LOWER(A2:A7),"late","")))/LEN("late"))`.

**How do I count cells that contain the word at least once?**

Use COUNTIF with wildcards: `=COUNTIF(A2:A7,"*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-occurrences-of-a-word-in-a-cell