Converters and Utilities

How to Count Words in Excel (Formulas + Specific Word)

Published 6 November 2026 · By the AI Mode Team

If you want to learn how to count words in Excel, start with one formula. Excel has no built-in word counter, but a short formula does the job. To count the words in a cell, use =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1. To count how many times one specific word appears, use =(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),LOWER("word"),"")))/LEN("word"). Below you will find the exact formulas for a single cell, a whole column, and specific words and repeated words, each with an example you can copy.

Need a quick answer without a spreadsheet? Paste your text into our free Word Counter.

Works in Excel 365, 2021, 2019, 2016, and Excel for Mac.

Does Excel Have a Word Count Feature?

No. Microsoft Word shows a live word count in its status bar, but Excel only counts cells, rows, and numbers. Excel can count words only through formulas. This works because a sentence with N words always has N-1 spaces between them. If you count the spaces and add 1, you get the number of words.

The formulas below also work in Google Sheets without changes.

How to Count Words in Excel in a Single Cell

Suppose cell A1 contains: Excel is easy to learn.

Enter this formula in B1:

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

The result is 5.

How the formula works

  1. TRIM(A1) removes extra spaces at the start, at the end and between words, so double spaces are not counted as extra words.
  2. LEN(TRIM(A1)) returns the total number of characters, including spaces.
  3. SUBSTITUTE(TRIM(A1),” “,””) removes every space, and LEN() of that returns the number of characters without spaces.
  4. The difference is the number of spaces. Adding 1 gives the number of words.

Handle empty cells. If A1 is blank, the formula above returns 1 instead of 0. Use this safer version:

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

How to Count Words in a Column or Range

To get the total words across A1:A10 in a single cell, use SUMPRODUCT:

=SUMPRODUCT(LEN(TRIM(A1:A10))-LEN(SUBSTITUTE(TRIM(A1:A10)," ",""))+(LEN(TRIM(A1:A10))>0))

This adds up the words of every cell in the range and treats empty cells as zero. Change A1:A10 to your own range, for example, A2:A500.

Tip: If you want a separate count for each row, put the single-cell formula in B1 and drag it down.

How to Count a Specific Word in a Cell

Put the word you are searching for in cell D1 (for example, Excel) and the text in A1. Then use:

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

This removes every occurrence of the word, measures how many characters disappeared, and divides by the length of the word. The result is how many times the word appears.

Text in A1Word in D1Result
Excel is great, and Excel is fast.excel2
to be or not to bebe2
The cat sat on the mat.the2

Case: LOWER() makes the count case-insensitive, so “Excel” and “excel” are treated as the same word.

One limitation: this method counts the letters wherever they appear, so searching for “cat” would also count the “cat” inside “category.” For whole-word accuracy, add spaces around the word: search for “cat” and place a space before and after the text ( =" "&A1&" " ).

how to count words in excel

How to Count a Specific Word in a Column

To count how many times a word appears across many cells, use SUMPRODUCT with the same logic:

=SUMPRODUCT((LEN(A1:A100)-LEN(SUBSTITUTE(LOWER(A1:A100),LOWER(D1),"")))/LEN(D1))

The word you are searching for sits in D1, and the data is in A1:A100. This is the right formula when you want the total number of occurrences, not just the number of cells that contain the word.

COUNTIF: Count Cells That Contain a Word

Sometimes you only need to know how many cells contain a word, not how many times it appears. That is what COUNTIF is for.

GoalFormula
Cells that contain “apple” anywhere=COUNTIF(A1:A100,"*apple*")
Cells that are exactly “apple”=COUNTIF(A1:A100,"apple")
Cells that start with “apple”=COUNTIF(A1:A100,"apple*")
Cells that contain the word typed in D1=COUNTIF(A1:A100,"*"&D1&"*")

The asterisk ( * ) is a wildcard that stands for any text. COUNTIF is not case-sensitive.

COUNTIF vs. the SUBSTITUTE method: If one cell says “apple apple apple,” COUNTIF counts it as 1 cell, while the SUBSTITUTE method counts 3 words.

How to Count Repeated Words in Excel

To see how often each value repeats in a list, first list the unique words in column C, then use:

=COUNTIF($A$1:$A$100,C1)

Drag it down to get the count next to each word. If you are working with a long text in one cell, split it into separate cells first with Data > Text to Columns and the space as the delimiter.

How to Count Characters Instead of Words

If the limit you care about is characters (for example, a meta description or a tweet), use

=LEN(A1)

For characters without spaces, use a space.

Common Problems and Fixes

Quick Alternatives

Frequently Asked Questions

How do I count words in Excel?

Use =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1. It counts the spaces between words and adds 1.

Is there a word count button in Excel?

No. Excel does not include a built-in word counter, so you need a formula or a tool such as Word or an online counter.

How do I count a specific word in an Excel column?

Use =SUMPRODUCT((LEN(A1:A100)-LEN(SUBSTITUTE(LOWER(A1:A100),LOWER(D1),"")))/LEN(D1)), with the word in D1.

What is the difference between COUNTIF and counting occurrences?

COUNTIF counts the cells that contain the word. The SUBSTITUTE method counts every time the word appears, including several times in one cell.

Do these formulas work in Google Sheets?

Yes. The same formulas work in Google Sheets.

Can I count words in Excel for Mac?

Yes. The formulas are identical on Windows and Mac.

Final Thoughts

Excel cannot count words by itself, but one formula can. Use the LEN, TRIM, and SUBSTITUTE formulas for a single cell, SUMPRODUCT for a range, and COUNTIF when you only need to know which cells contain a word. For longer text, paste it into our free Word Counter.

Free tools for this

Everything runs in your browser. Your files never leave your device.

More Converters and Utilities

One Response

Leave a Reply

Your email address will not be published. Required fields are marked *