Count words in Excel cells

Excel doesn’t natively work with “words” as an entity — it only recognizes cells and the characters inside them. Spaces are just another character, not a logical word separator. As a result, unlike Word, Excel has no built-in word count feature. But with the right approach, we can count words in several ways. Let’s explore them!

Formula to Count Words in an Excel Cell

The classic trick is simple: if you

• remove punctuation so that hyphens and dashes aren’t counted as separate words
• remove extra spaces
• exclude empty cells

…then the number of words in a cell is always one more than the number of spaces in it.

The simplest formula looks like this:

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

This formula doesn’t handle cases where the cell is empty or contains only spaces. There are two ways to fix that:

Option 1: Check the trimmed length first and return 0 if there’s no text.

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

Option 2 (shorter): Add a placeholder word (like a period and a space) to the cell, then remove the +1 from the formula.

=LEN(TRIM(A1&" ."))-LEN(SUBSTITUTE(TRIM(A1&" .")," ",""))

Count Specific Words in a Cell

If you want to count how many times a specific word appears in a cell:

• Measure the length of the original string
• Use SUBSTITUTE to remove that word (converting both text and target to the same case using LOWER or UPPER)
• Measure the length again
• Subtract the lengths and divide by the length of the word

Formula:

=(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),LOWER(B1),"")))/LEN(B1)

For exact matches only, you’ll need to:

• Add extra spaces before and after the text
• Double the spaces between words
• Remove punctuation first
• Convert both strings to the same case

Formula for exact word matches:

=(LEN(" "&SUBSTITUTE(A1," ","  ")&" ")-LEN(SUBSTITUTE(" "&SUBSTITUTE(A1," ","  ")&" "," "&B1&" ","")))/LEN(" "&B1&" ")

Count Specific Words in a Range

If you need to know in how many cells a word appears at least once, use COUNTIF. If you need the total occurrences across all cells (including duplicates in the same cell), you’ll need an array formula similar to the single-cell version above.

Count All Unique Words with Frequency

Excel doesn’t have a built-in feature for this, so I created one in the !SEMTools add-in. It counts not just words but also n-grams (two-word, three-word phrases, etc.) and can generate pivot tables with metrics linked to the text.

Count Words in Each Cell – !SEMTools

The !SEMTools add-in has a dedicated “Count words in cells” procedure located under the EXTRACT menu. It works instantly across ranges and is available in Lite and Pro versions.

It also includes a custom wordCount function:

=wordCount(A1)

These return the word count for any cell. Just download and install the free !SEMTools version to use them.

This post is also available in RU.