Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to count occurrences of a word in a cell in Excel and Google Sheets
Topic:Text manipulationExcel & Google Sheets
=(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER(B2),"")))/LEN(B2)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Text | Word | Count |
| the cat sat on the mat | the | |
| apple banana apple | apple | |
| hello world | world | |
| one one one one | one |
=(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER(B2),"")))/LEN(B2)→2
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Put the text you want to search in column A (e.g., A2) and the word to count in column B (e.g., B2).
- 2Enter the formula =(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER(B2),"")))/LEN(B2) in an empty cell.
- 3LOWER() makes the search case-insensitive. Remove LOWER() if you need case-sensitive counting.
- 4The formula subtracts the length after removing the word from the original length, then divides by the word length to get the count.
- 5Press Enter. The result is the number of times the word appears in the cell.
Tips
- Wrap the result in INT() to avoid decimal artifacts from partial matches: =INT((LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER(B2),"")))/LEN(B2)).
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Does this count partial matches, like 'cat' inside 'catch'?
Yes. SUBSTITUTE replaces all occurrences including partial matches. To count whole-word matches only, pad the search word with spaces or use more advanced pattern matching.
How do I count occurrences across a whole range of cells?
Use COUNTIF for whole-cell matches, or sum the per-cell formula over a range: =SUMPRODUCT((LEN(A2:A6)-LEN(SUBSTITUTE(LOWER(A2:A6),LOWER(B2),"")))/LEN(B2)).
More on Text manipulation
See all →How-to
How to split text into columns
Split a full name or any delimited text into separate columns. Google Sheets uses SPLIT; Excel uses TEXTSPLIT — here is each.
How-toHow to remove extra spaces
Clean leading, trailing, and double spaces with TRIM — the usual fix for lookups that fail on values that look identical.
How-toHow to convert text to a number
Turn numbers stored as text into real numbers with VALUE or by multiplying by 1 — the fix for sums that ignore values.
How-toHow to extract the domain from an email address
Pull everything after the @ from an email using FIND and MID. Works the same in Excel and Google Sheets.
How-toHow to extract the first name from a full name
Get the first name from a full name with LEFT and FIND, splitting on the first space. Works in Excel and Google Sheets.
How-toHow to add leading zeros
Pad numbers to a fixed width with leading zeros using TEXT, or a custom number format. Works in Excel and Google Sheets.