How to count occurrences of a substring in a cell
✓ Verified in LibreOffice 25.8.7.3How many times a word or code appears inside one text value.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =(LEN(A2)-LEN(SUBSTITUTE(A2,B2,"")))/LEN(B2) | A2 = the text, B2 = the substring. Case-sensitive. |
| Google Sheets | =(LEN(A2)-LEN(SUBSTITUTE(A2,B2,"")))/LEN(B2) | Identical. |
| LibreOffice Calc | =(LEN(A2)-LEN(SUBSTITUTE(A2,B2,"")))/LEN(B2) | Identical. |
How it works
Deleting every copy of the substring shortens the text by (count × substring length), so dividing the shrinkage by LEN(B2) recovers the count: removing both "cat"s drops 6 characters, 6/3 = 2. The single-character version is the comma-counting recipe. SUBSTITUTE is case-sensitive — wrap both haystack and needle in LOWER() for case-blind counting — and note overlapping matches ("aaa" containing "aa") count non-overlapping occurrences only.
Verified, not just documented
We ran =(LEN(A2)-LEN(SUBSTITUTE(A2,B2,"")))/LEN(B2) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 2 — exactly the expected result. Every formula here is confirmed by actually executing it.