Excel Word Count Formula: Count Words in Cells and Text

To count words in a cell in Excel, use =IF(TRIM(A2)="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1), replacing A2 with the cell you want to check. It returns zero for a blank or space-only cell and otherwise counts words separated by ordinary spaces.

How the basic word-count formula works

The formula counts spaces after TRIM removes leading and trailing spaces and reduces repeated ordinary spaces between words to one. It then adds one: a text string with four words normally has three spaces separating them. LEN measures the text length, while SUBSTITUTE removes each space; the difference between the two lengths is the number of spaces.

For example, with the sentence “Excel formulas can count words.” in A2, the formula returns 4. The period stays attached to “words.”, but the formula does not treat punctuation as a separate word. This is a practical count of space-separated items, not a linguistic analysis of the sentence.

Enter the formula in another cell, such as B2, and press Enter. To calculate counts for multiple rows, drag the fill handle at the lower-right corner of B2 down the column, or copy B2 and paste into the cells below. Excel adjusts A2 to A3, A4, and so on as the formula is filled down.

Count words in a column and total them

To get one count for each text entry, put the per-cell formula in a neighboring column and fill it down. This keeps the result easy to inspect: each row has its own word count, so you can identify which entry is unusually short or long. It is also more useful than one grand total when the text represents separate responses, product descriptions, or article drafts.

With individual counts in B2:B4, where the rows contain counts of 4, 3, and 0, use this formula in B5 to add them:

With word-count results in B2:B4 (4, 3, and 0), the formula returns 7 total words.

=SUM(B2:B4)

The total is the sum of the three row-level counts. A blank source cell contributes zero when you use the IF-based formula above. If you instead want a count for every text cell without a separate helper column, the options depend on the Excel version and how complex the text is; a helper column is generally easier to audit and maintain.

Handle tabs, line breaks, and nonbreaking spaces

The basic formula is suitable when words are separated by ordinary spaces. Text copied from websites, PDFs, email, or other applications may also contain tabs, line breaks, or nonbreaking spaces. Those characters can make a space-only count inaccurate because Excel does not treat every kind of whitespace as the same character.

In Microsoft 365 for Windows, this version replaces common whitespace characters with ordinary spaces before counting. It accounts for a nonbreaking space (CHAR(160)), a tab (CHAR(9)), a line feed (CHAR(10)), and a carriage return (CHAR(13)).

With the text to count in A2, including any of those whitespace characters, use this formula; for example, “Excel” followed by a tab and “formulas” followed by a line break before “can count words” returns 5:

=LET(cleaned,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160)," "),CHAR(9)," "),CHAR(10)," "),CHAR(13)," "),trimmed,TRIM(cleaned),IF(trimmed="",0,COUNTA(TEXTSPLIT(trimmed," ",,TRUE))))

LET gives names to intermediate results, so the formula can clean the text once and then use that cleaned version. TEXTSPLIT separates the text at spaces, and its TRUE argument tells it to ignore empty pieces. The IF test prevents an empty string from being counted as one item. TEXTSPLIT is available in Microsoft 365 and Excel 2024; it is not available in some older perpetual Excel releases.

The formula handles these common characters, not every possible Unicode spacing character. If the source data contains unusual formatting, inspect a sample or clean it in a dedicated step before relying on the count. The basic formula and the newer formula also follow the same practical convention: a sequence between recognized separators is one word.

Choose the right formula for your Excel version

For ordinary typed text with spaces between words, the basic LEN, SUBSTITUTE, and TRIM formula is a good default. These are long-standing Excel functions, so the formula works in many older versions as well as Microsoft 365. It is short, readable, and does not require a special array or text-splitting function.

Use the LET and TEXTSPLIT version when entries may contain tabs or line breaks and your Excel version supports TEXTSPLIT. Microsoft 365 and Excel 2024 include TEXTSPLIT. If you are working in an older Excel version, use the basic formula for consistently spaced text or clean the imported text first; do not assume that a formula using TEXTSPLIT will work in every installation.

In Excel for the web, function availability can depend on the version and update state of the account, but current Microsoft 365 web Excel supports modern functions such as TEXTSPLIT. On a Mac, the same formulas work where the corresponding functions are available; the main differences are usually in the interface and keyboard shortcuts, not in the formula syntax. All examples here use commas as argument separators. Some regional Excel settings require semicolons instead.

What Excel counts as a word

These formulas count text tokens separated by the recognized whitespace characters. They do not apply a dictionary, grammar rules, or a word processor’s editorial conventions. Consequently, punctuation usually remains part of a token, and punctuation marks on their own may be counted if separated by spaces.

“state-of-the-art” is counted as one token because it contains no space.
“don’t” is counted as one token, and “well, done” as two.
“one/two” is counted as one token because a slash is not a separator in these formulas.
A standalone dash surrounded by spaces is counted as a token, just like any other space-separated item.

This distinction matters for word limits. If a publisher, school, or form defines how to count hyphenated terms, contractions, URLs, or punctuation, use that definition rather than assuming Excel’s result will match it. The formula is best understood as a consistent spreadsheet count, not a universal standard for word counting.

Common problems and how to fix them

A blank cell shows 1: A formula that simply adds one to the number of spaces can return one for an empty string. Keep the IF check so blank or space-only input returns zero.
The count seems too high: Look for tabs, line breaks, or nonbreaking spaces, or check whether punctuation or a standalone symbol is being treated as a token. Use the whitespace-cleaning formula when appropriate.
The count seems too low: Check whether the text contains several words joined by punctuation without spaces, such as “north/south.” These formulas count that as one token. Add spaces only if that matches the counting rule you need.
Excel displays an error for TEXTSPLIT: Your Excel release may not include that function. Use the basic formula for ordinary spaces or open the workbook in a supported Microsoft 365 or Excel 2024 version.
The formula appears as text: The destination cell may be formatted as Text, or the formula may have a leading apostrophe. Change the cell format to General, then re-enter the formula.
The formula uses the wrong separator: Excel’s list separator follows regional settings. If Excel rejects commas, your installation may expect semicolons between arguments; use the separator shown by your regional configuration.

When checking an unexpected answer, test the source cell with a small, known example and compare its separators. You can also paste the text into a plain-text editor to reveal line breaks or unusual spacing, although some invisible characters may still require a cleanup step. Avoid editing a large imported dataset until you have confirmed the cleanup rule on representative rows.

Formula versus manual and word-processor counts

A formula is useful when counts must update as text changes, when a sheet contains many entries, or when you need to flag rows outside a target range. A manual count is manageable for a single short line, but it is slow and difficult to repeat consistently across hundreds of records. A word processor may use more sophisticated language-aware rules, so its count can differ from a spreadsheet count, especially around hyphens, punctuation, and special whitespace.

For a quick check of a long passage, pasting text into a word processor’s word-count feature may be more convenient. For ongoing tracking inside a spreadsheet, Excel formulas keep the count beside the text and recalculate when the source changes. Neither approach should be assumed to match a third party’s policy unless that policy’s rules are known.

Make the result easier to use

For a worksheet with many rows, label the helper column clearly, such as “Word count,” and keep the formula beside its source text. You can then sort or filter by the count, or compare it with a target. If you convert the data range to an Excel table using Insert > Table, formulas entered in a table column can fill down automatically as new rows are added.

For a simple limit check, add a separate status column rather than replacing the count. For example, a reader can compare the numeric count with a maximum and display a message. This is helpful for review, but remember that a cell formula cannot guarantee that a text field follows a particular publisher’s counting policy. Keep the original text intact and use the count as a check, not as a substitute for reading the content.

Frequently Asked Questions

What is the simplest Excel formula to count words?
For text in A2 separated by ordinary spaces, use =IF(TRIM(A2)="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1). It returns zero for blank or space-only input and counts space-separated tokens otherwise.

Can Excel count words in an entire column?
Yes. Put a per-cell formula in a neighboring column, fill it down for each row, and use SUM on the result cells if you need a total. A helper column also lets you see each individual count.

Does the formula count hyphenated words as one or two?
It counts a hyphenated expression with no spaces as one token. The formula splits on spaces, not hyphens, so “part-time” is one; a policy that treats it as two needs a different counting rule.

Why does my word count differ from Microsoft Word?
Excel’s formula counts tokens separated by specified whitespace characters, while a word processor may apply different rules to punctuation, hyphens, or other text. Check the relevant word-count policy if an exact match matters.

Can I count words separated by line breaks?
Yes, if you normalize line breaks before counting. The LET and TEXTSPLIT formula above replaces common line-break characters with spaces, but requires Microsoft 365 or Excel 2024.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.