How to Count Words in Excel

Excel has no word count feature, no ribbon button and no formula function with that name. What it does have is enough string handling to count words with one line of arithmetic, and that calculation is more flexible than a button would be.

The formula

For text in cell A1, this is the word count

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

It works by measuring the length of the text, then measuring the length again with every space removed. The difference is the number of spaces, and the number of words is one more than the number of gaps between them. TRIM collapses runs of multiple spaces and strips leading and trailing spaces, so double spacing does not inflate the result.

Handling empty cells

An empty cell returns one, which is wrong. Wrap it

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

Now a blank cell returns zero and your column total stays honest.

Counting a whole column

Put the single cell formula in B1, fill it down the column, then total it

=SUM(B1:B200)

If you would rather not add a helper column, Excel 365 and Excel 2021 support array evaluation, so this works in one cell

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

The comma problem

Every formula above treats spaces as the only word separator. Text copied out of a PDF or a website often separates words with non breaking spaces, which look identical and are a different character. Those will merge two words into one.

Two fixes. Clean the text first with Find and Replace, pasting a non breaking space into the find box. Or use Excel's own cleanup by running the text through =SUBSTITUTE(A1,CHAR(160)," ") before counting.

Why not just use Word

Because the text is already in a spreadsheet with the rest of your data. Product descriptions in a catalogue, translations in a review queue and survey answers in a column all need their length checked in place, next to the things they relate to. Copying five hundred rows into a word processor is not a workflow.

A spreadsheet formula that handles edge cases beats a button that only handles the easy case. Spend two minutes getting the formula right and it will work on every file you open for years.