How to count occurrences of a substring in a cell
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)How many times a word or code appears inside one text value.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =(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. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 2 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
LEN · SUBSTITUTE — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to count items in a comma-separated cell
- How to count the total words in a range
- How to count the number of words in a cell
- How to capitalize only the first letter (sentence case)
- How to convert a column number to a letter