Count Words in a Cell

All versionsLENSUBSTITUTETRIM

Excel has no WORDCOUNT function, but counting words is a classic trick: count the spaces between words and add one. LEN, SUBSTITUTE, and TRIM do it cleanly even with messy spacing.

Quick formula: to count the words in A2:

=LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2), " ", "")) + 1

The length difference is the number of spaces; words = spaces + 1. TRIM first so extra spaces don’t inflate the count.

Functions used (tap for the full reference guide):

LEN SUBSTITUTE TRIM

The example

Word counts for a few phrases.

	A	B
1	Phrase	Words
2	hello world	2
3	the quick brown fox	4
4	one	1

The formula

The word count in B2:

=LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2), " ", "")) + 1 // "the quick brown fox" → 4

How it works

Counting spaces is the whole idea:

TRIM(A2) first collapses runs of spaces to one and strips the ends, so messy input doesn’t over-count.
LEN(TRIM(A2)) is the length with single spaces; LEN(SUBSTITUTE(…, " ", "")) is the length with no spaces.
The difference equals the number of spaces between words.
Words = spaces + 1, so add 1. “the quick brown fox” has 3 spaces → 4 words.

An empty cell would return 1. Guard it: =IF(TRIM(A2)="", 0, LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1) so blanks count as 0 words.

Try it: interactive demo

Live demo

Type a phrase (extra spaces are handled).

Phrase (A2)

Words:

Variations

Guard against blank cells

Return 0 for empty input:

=IF(TRIM(A2)="", 0, LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)

Count occurrences of a specific word

How many times “the” appears:

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

Count items in a comma list

Count commas + 1 instead of spaces:

=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1

Pitfalls & errors

Empty cell returns 1. With no words there are no spaces, so the +1 gives 1. Wrap in the IF guard shown above.

TRIM is essential. Without it, double spaces inflate the count. Always TRIM inside both LEN calls.

Punctuation isn’t a separator. “one,two” (no space) counts as one word. SUBSTITUTE punctuation to spaces first if needed.

Practice workbook

📊

Download the free Count Words in a Cell practice workbook

Phrases with the live word-count formula, the blank-guard and count-a-word variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I count words in a cell in Excel?

Count the spaces and add one: =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1. TRIM handles extra spaces so the count stays accurate.

How do I make an empty cell return 0 words?

Wrap it in an IF: =IF(TRIM(A2)="", 0, LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1).

How do I count how many times a specific word appears?

Use =(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),"the","")))/LEN("the") to count occurrences of "the".

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

Function references: LEN · SUBSTITUTE · TRIM