Text & String Manipulation
Count Whole-Word Occurrences in a Cell Without Partial Substring False Positives
Count how many times a standalone target word appears inside a sentence or log message by normalizing punctuation to spaces and padding word boundaries before measuring LEN - LEN(SUBSTITUTE).
Why naive SUBSTITUTE counts "cat" inside "catalog" and "category"
The classic character-length difference (LEN(text) - LEN(SUBSTITUTE(UPPER(text), UPPER(word), ""))) / LEN(word) matches substrings indiscriminately, so searching for "CAT" in "CAT5 cable in catalog for cat-scan" falsely counts 3 hits instead of 1 standalone word.
Replacing punctuation delimiters (commas, periods, semicolons) with spaces, collapsing whitespace via TRIM, and wrapping both the haystack and needle in single spaces (" " & text & " ") forces SUBSTITUTE to remove only whole-word tokens.
| Input Text | Target Word | Substring Count (Buggy) | Delimiter-Padded Whole-Word Count |
|---|---|---|---|
| cat in catalog category | cat | 3 | 1 |
| API error, error! retry error. | error | 3 | 3 |
| pro-plan upgrade to pro | pro | 2 | 1 ("pro-plan" kept hyphenated) |
Case-Insensitive Whole-Word Count Formula
Normalize commas, periods, and exclamation marks into spaces inside LET, double interior spaces so adjacent identical words ("cat cat") each own a dedicated left and right boundary space, and divide the removed character count by LEN(token).
=LET(norm_txt, TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A2), ",", " "), ".", " "), "!", " ")), clean_txt, " " & SUBSTITUTE(norm_txt, " ", " ") & " ", token, " " & TRIM(UPPER(B2)) & " ", IF(TRIM(B2) = "", 0, (LEN(clean_txt) - LEN(SUBSTITUTE(clean_txt, token, ""))) / LEN(token)))Try the example
Use the original values or change them to test your understanding.