Count words in Excel cells

• Formula to count words in an Excel cell
• Count specific words in a cell
• Count specific words in a range
• Count all unique words with their frequency
• Count words in each cell in 2 clicks with !SEMTools

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:

• Remove punctuation first
• Add extra spaces before and after the text
• Double the spaces between words (Find and replace tool will work)
• 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 their frequency

Excel doesn’t have a built-in feature for this, so I created it myself. 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.

You can read about it here: N-gram analysis, building frequency dictionary in Excel

Count words in each cell in 2 clicks with !SEMTools

The !SEMTools add-in has a dedicated “Count words in cells” procedure located under the “Attributes” -> “of text” menu in EXTRACT group of tools. It works instantly across ranges and is available in both Lite and Pro versions of !SEMTools. Output is limited in trial version.

It also includes a custom wordCount function, which is free to use:

=wordCount(A1)

It will return the word count for any cell. Just download and install the free !SEMTools version.

This post is also available in RU.