Text
Count how many times a word or substring appears in a cell
Divide the characters removed by SUBSTITUTE by the length of the search word, and guard empty search terms against #DIV/0!.
Divide total removed characters by search-term length
When SUBSTITUTE deletes a 5-letter word like "error" three times, the string shrinks by 15 characters. Dividing that character reduction by LEN(SearchWord) (15 / 5) yields 3 occurrences.
Always guard blank search cells with IF(COUNTBLANK(B2:B2)=1, 0, ...); otherwise an empty search string divides 0 by 0 and produces #DIV/0!.
This counts non-overlapping substrings, not whole-word boundaries: error also matches inside terror. The case-insensitive formula assumes ASCII text because Unicode uppercasing can change string length.
| LogEntry (A) | SearchWord (B) | Case-Insensitive | Case-Sensitive |
|---|---|---|---|
| error: disk error after timeout error | error | 3 | 3 |
| Warn: retry warn on node-2 | warn | 2 | 1 |
Case-insensitive vs exact-case substring count
Wrapping both LogEntry and SearchWord in UPPER counts "Warn" and "warn" together (2 matches), while omitting UPPER counts only lowercase "warn" (1 match).
=IF(COUNTBLANK(B2:B2)=1, 0, (LEN(A2) - LEN(SUBSTITUTE(UPPER(A2), UPPER(B2), ""))) / LEN(B2))Empty search term boundary (#DIV/0!)
Dividing by LEN("") without a blank guard divides 0 by 0 (#DIV/0!).
=(LEN(A3) - LEN(SUBSTITUTE(A3, B3, ""))) / LEN(B3)Try the example
Use the original values or change them to test your understanding.