How to Count Words and Characters in a Cell in Excel

Q: How do I count the number of characters in an Excel cell?

Use =LEN(A2). It returns the total count of letters, numbers, spaces, and punctuation in the cell. To count characters across a range, use =SUMPRODUCT(LEN(A2:A100)). LEN also counts trailing spaces, so trim stray whitespace first if it will skew the total.

The fastest ways to count words and characters in Excel

To count words and characters in Excel, use =LEN for characters, a SUBSTITUTE formula for words, or a free macro that writes both counts next to a whole column in one pass. Character count is a one-line formula. Word count is the awkward part, because Excel has no word-count function at all.

Which approach fits depends on whether you need live formulas or a fast count across a full column. Here's the short version first.

Method	Counts	Live or static?	One click?
LEN formula	Characters	Live	No, fill down
SUBSTITUTE formula	Words	Live	No, fill down
Free macro	Words and characters	Static	Yes

TL;DR: Key takeaways

To count characters in a cell, use =LEN(A2). It counts letters, numbers, spaces, and punctuation.
Excel has no word-count function. Count words with =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1.
That word formula breaks on empty cells (it returns 1, not 0) and is easy to mistype.
The free macro writes both a word count and a character count beside every cell in one pass.
Formulas stay live. The macro writes static numbers, so rerun it if the text changes.

Method 1: Count characters with LEN

To count the characters in a cell, use the LEN function: =LEN(A2).

LEN counts everything inside the cell: letters, digits, spaces, and punctuation. Drop =LEN(A2) in B2, press Enter, and fill down. To total the characters across a range, use =SUMPRODUCT(LEN(A2:A100)). Microsoft's guide to counting characters in cells covers the variations.

One gotcha: LEN counts trailing spaces, so hello returns 6, not 5. If stray whitespace will skew your counts, trim the whitespace first.

Method 2: Count words with a formula

Excel has no WORDCOUNT function, so you count words by counting the gaps between them and adding one:

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

Here's the logic. TRIM removes extra spaces, SUBSTITUTE strips out the remaining spaces, and the difference in length tells you how many gaps sat between words. Add one, and you have the word count.

It works, but it's fragile. An empty cell returns 1 instead of 0. Text separated by line breaks instead of spaces miscounts. And the formula is long enough that a stray parenthesis is easy to introduce. For a single cell it's fine. For a column of 200 essay answers, it's a liability.

Method 3: The free macro (both counts, whole column)

To count words and characters for an entire column at once, use a macro. Download Count Words and Characters. It's a free .xlsm file with one macro. No install, no signup, and it runs offline so your data stays on your machine.

First time running one of our macros? See How to run our tools for the 30-second setup.

Select your single column of text, including the header row on top.
Press Alt + F8, pick CountWordsAndCharacters, and click Run.
The macro labels the next two columns "Words" and "Characters" and fills a count for every cell below.

It splits words on any whitespace, so spaces, tabs, and line breaks all count as one gap, and it counts characters the way LEN does. The top row of your selection is treated as the header, so include your header row. The two columns to the right get overwritten, so insert blank columns there first if they hold data.

Example: checking essay length for a class

A high school English teacher in Leeds pastes 120 student paragraph responses into a column to check they hit the 150 to 200 word target. The word-count formula would mean typing that fragile SUBSTITUTE line and hoping no blank submissions throw it off.

She selects the column with its header, runs the macro, and gets a Words column and a Characters column beside the responses in one pass. She sorts by Words, and the three under-length answers rise to the top. A check that would have meant a formula and a squint takes about 5 seconds.

When a formula is the better choice

Two honest limits, so you pick the right tool.

The counts are static. The macro writes numbers, not formulas, so if a student edits an answer later, the count won't move. Rerun the macro, or use =LEN and the word formula when you need the counts to update live.

It overwrites the next two columns. The macro writes into the two columns immediately to the right without asking. If those hold data, insert two blank columns first.

For related jobs, extract numbers from text pulls figures out of the same kind of free-text column, and summarize a column with mean and median turns the word counts into class-level stats like average essay length. Both sit inside the guide to Excel productivity macros.

FAQ

How do I count the number of characters in an Excel cell?

Use =LEN(A2). It returns the total count of letters, numbers, spaces, and punctuation in the cell. To count characters across a range, use =SUMPRODUCT(LEN(A2:A100)). LEN also counts trailing spaces, so trim stray whitespace first if it will skew the total.

How do I count words in a cell in Excel?

Excel has no word-count function, so count the spaces and add one: =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1. For a whole column, the free macro is safer, because the formula returns 1 for an empty cell and miscounts text separated by line breaks.

Does Excel have a word count like Word?

No. Excel has no built-in word count feature and no status-bar word counter. You either build the SUBSTITUTE formula above for each cell, or run a macro that writes a word count next to every cell in the column at once.

Do the counts include spaces and punctuation?

The character count includes spaces and punctuation, exactly like LEN. The word count ignores how many spaces sit between words, because runs of whitespace count as a single separator, so double spaces and line breaks don't inflate the total.

Does this work on Excel for the web or Mac?

The macro runs on Excel for Windows and Mac, version 2016 and later. It does not run in Excel for the web, which has no VBA. LEN and the word-count formula work everywhere, including the web.

What to do next

Use =LEN for a quick character count, the SUBSTITUTE formula when you need live word counts, and the macro when you want both for a whole column in one pass.

Download the free Count Words and Characters macro and run it on your own data. It's the quickest way to count words and characters in Excel when you have a column of essays, posts, or notes to measure.