How to Count Words in Excel (Formulas + Specific Word)
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
- TRIM(A1) removes extra spaces at the start, at the end and between words, so double spaces are not counted as extra words.
- LEN(TRIM(A1)) returns the total number of characters, including spaces.
- SUBSTITUTE(TRIM(A1),” “,””) removes every space, and LEN() of that returns the number of characters without spaces.
- 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 A1 | Word in D1 | Result |
|---|---|---|
| Excel is great, and Excel is fast. | excel | 2 |
| to be or not to be | be | 2 |
| The cat sat on the mat. | the | 2 |
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 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.
| Goal | Formula |
|---|---|
| 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
- The result is 1 for an empty cell. Use the IF(LEN(TRIM(A1))=0,0, …) version shown above.
- The count is too high. The text may contain double spaces or non-breaking spaces. TRIM removes normal extra spaces; for non-breaking spaces, first run
=SUBSTITUTE(A1,CHAR(160)," "). - #VALUE! error. Check that you are not pointing the single-cell formula at a range. Use the SUMPRODUCT version for ranges.
- Hyphenated words. “well-known” counts as one word because there is no space in it.
- Different regional settings. In some countries Excel uses semicolons instead of commas in formulas (for example, SUBSTITUTE(A1;” “;””)).
Quick Alternatives
- Copy the cell text into Word or Google Docs. Word shows a live count, and Docs does too. Our main guide explains how to count words in Word and Google Docs.
- Use an online counter: paste the text into our Word Counter and get words, characters, and reading time instantly.
- For the general methods, see our main guide: How to Count Words.
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.
One Response