Word Count in Excel: Formulas for a Cell or a Column
Blog / SPREADSHEETS
Excel does not have a Word Count dialog like Microsoft Word. For ordinary text separated by spaces, you can calculate the word count in a cell with LEN, TRIM and SUBSTITUTE. The important part is handling blank cells and extra spaces before counting the gaps between words.
Count words in one cell
Put your text in A2 and enter the formula below in B2. TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces to one. LEN measures the cleaned text. SUBSTITUTE removes its spaces, so subtracting the two lengths gives the number of gaps. Add one to turn gaps into words.
The IF check is essential: without it, an empty cell can incorrectly return one word. This formula counts space-separated pieces of text, not linguistic words. A standalone punctuation mark surrounded by spaces will count as a piece too.
=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)Check the formula with simple examples
A2 contains 'Write clearer sentences': the result is 3. If A2 contains ' Write clearer sentences ', the result is still 3 because TRIM normalizes the spaces. A blank cell or a cell containing only ordinary spaces returns 0.
A hyphenated term such as 'well-written' remains one piece because there is no space inside it. 'Writing & editing' returns 3, including the ampersand. If your assignment uses a different convention, the submission system's count takes priority.
Count a whole column without counting the heading
Copy B2 down beside every populated row in column A. If your data ends at row 100, use =SUM(B2:B100) to total the individual counts. Keep the column heading in row 1 outside this range.
Using a helper column makes mistakes easier to inspect than a single large formula. Check a few short and long cells manually before using the total in a report. If the sheet contains several text columns, count each relevant column separately and sum the helper columns.
Clean pasted line breaks and tabs first
TRIM alone does not convert tabs, line breaks or nonbreaking spaces into ordinary spaces. Text pasted from websites or multi-line cells can therefore be undercounted. The formula below creates a cleaned version of A2 in C2; apply the word-count formula to C2 instead of A2.
Replace these separators with spaces, rather than deleting them, so the end of one word does not merge with the start of the next. These substitutions cover common cases, not every possible Unicode separator.
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160)," "),CHAR(10)," "),CHAR(13)," "),CHAR(9)," "))Word counts are not text-cell counts
COUNTA counts nonempty cells, not the words inside them. A single cell containing a 100-word paragraph still contributes only one nonempty cell. COUNTIF answers matching-cell questions, which is also different from counting every word.
For a one-off check, copy the relevant cell text into Top Word Counter. Avoid copying row headings or unrelated columns. Text calculations run in your browser; a spreadsheet formula is still the more convenient option when you need counts to update with every row edit.
Frequently asked questions
Does Excel have a built-in word counter?
Not a Word-style Word Count dialog. You can calculate counts with text formulas or copy selected text into a word counter.
Why does my formula return one for a blank cell?
A spaces-plus-one formula needs a blank-text check. Use the IF guard shown above so empty cleaned text returns zero.
Will this formula work for text without spaces?
It counts space-separated pieces. It cannot reliably segment languages or writing systems that do not separate words with spaces.