Count How Many Times a Word Appears

All versionsSUBSTITUTE

How many times does a word appear in a cell? Measure how much shorter the text gets when you remove the word, then divide by the word’s length — a classic LEN/SUBSTITUTE count.

Quick formula: count “the” in A2:

=(LEN(A2) - LEN(SUBSTITUTE(LOWER(A2),"the",""))) / LEN("the")

The length lost by removing all “the”s, divided by the length of “the,” is the count. LOWER makes it case-insensitive.

Functions used (tap for the full reference guide):

SUBSTITUTE LEN

The example

Counting a word inside a sentence.

	A	B
1	Text	Count of "the"
2	the cat and the dog	2

The formula

Length difference divided by word length:

=(LEN(A2) - LEN(SUBSTITUTE(LOWER(A2),"the",""))) / LEN("the") // counts "the" twice

How it works

Removal reveals the count:

SUBSTITUTE(LOWER(A2), "the", "") deletes every “the”; the text gets shorter.
The length lost = LEN(A2) − LEN(removed) — the total characters of all the “the”s.
Divide by LEN("the") to get the number of occurrences.
LOWER on both makes the count case-insensitive; drop it for case-sensitive counting.

Substrings count too. Counting “the” will also catch it inside “there” and “other.” To count whole words, pad with spaces — count " the " in " "&A2&" " — so only standalone words match.

Try it: interactive demo

Live demo

Text and a word to count.

Word

Count:

Variations

Whole words only

Pad with spaces:

=(LEN(" "&A2&" ")-LEN(SUBSTITUTE(" "&LOWER(A2)&" "," the ","")))/LEN(" the ")

Case-sensitive

Drop LOWER:

=(LEN(A2)-LEN(SUBSTITUTE(A2,"The","")))/LEN("The")

Count across a range

Sum per cell:

=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,w,"")))/LEN(w))

Pitfalls & errors

Substrings inflate the count. “the” matches inside “there.” Pad with spaces to count whole words.

Empty word divides by zero. Guard against a blank search term.

Case. LOWER both sides for case-insensitive; otherwise it’s exact-case.

Practice workbook

📊

Download the free Count How Many Times a Word Appears practice workbook

A word-counter with whole-word, case-sensitive, and range variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I count how many times a word appears in a cell in Excel?

Use =(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),"word","")))/LEN("word"). The length removed divided by the word length is the count.

How do I count only whole words?

Pad both the text and the word with spaces, e.g. count " the " inside " "&A2&" ", so substrings inside other words do not match.

How do I make it case-sensitive?

Remove the LOWER wrapper so SUBSTITUTE matches the exact case.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Count words · Find Nth occurrence · Substitute multiple

Function references: SUBSTITUTE · LEN