Count Words and Characters Per Cell

What it does

Excel has =LEN() for character count and a clever workaround using LEN(TRIM()) and SUBSTITUTE for word count, but cobbling them together for a column of text means writing two helper formulas, copying them down, and remembering which is which. The macro does both at once. Select your column of text, run it, and word and character counts appear in the two columns to the right with proper headers. Whitespace is normalized, so a sentence with a tab and a newline counts the same as the same sentence with single spaces.

Real-world example

Picture a high school English teacher grading the weekly short-response prompts for her four sections of 30 students each. The prompts have a target length: "Respond in 75 to 150 words. Anything shorter is incomplete; anything longer probably wandered." Students paste their responses into a shared Google Sheet she imports to Excel each Friday for grading.

Her current method: eyeball each response, mentally estimate "looks short" or "looks long", spot-check the iffy ones with =LEN(B2)/5 or by pasting into Word. About 20 seconds per response. With 120 responses, that's 40 minutes of length-checking before she even reads for content. Most weeks she gives up and just grades on content, which means she occasionally rewards a response that hit none of the length requirements.

With this macro she selects the response column, runs the tool, and 120 word counts and character counts appear in the two columns to the right in 2 seconds. She sorts by word count, immediately sees the 9 responses under 75 words and the 4 over 150, gives those the appropriate length penalty, and reads the rest for content. Total elapsed: 30 seconds for the length pass.

On a typical 120-response weekly grading pass, expect about 35 minutes saved on length-checking alone. Across a school year that's about 25 hours back, and consistent enforcement of the length policy without adding work.

How to use

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

1. Make sure the two columns to the right of your text column are empty. The macro writes Words to column +1 and Characters to column +2.
2. Select the single column of text you want to measure (one column only).
3. Press Alt + F8 (Windows) or Option + F8 (Mac), pick CountWordsAndCharacters, click Run.
4. The macro writes word and character counts in the two adjacent columns and reports a count.

FAQ

How does the macro count words?

It normalizes whitespace first (replacing tabs and newlines with single spaces, collapsing runs of whitespace), then splits on space and counts non-empty parts. So Hello world. and Hello world. and Hello\tworld. all count as 2 words. Empty cells get no entry; cells with only whitespace count as 0 words.

Are punctuation and contractions handled correctly?

Punctuation attached to words counts as part of the word, so don't is 1 word and cat, is 1 word. Standalone punctuation (rare) counts as a word: ! ! ! is 3 "words". This matches the behavior of Microsoft Word's word counter for most realistic text.

What does the character count include?

LEN(text) from VBA: every character in the cell, including spaces, punctuation, and trailing whitespace. So Hello world. is 12 characters (5 + 1 + 5 + 1). If you want a count without trailing whitespace, run Trim Whitespace From All Cells first.

Will it overwrite anything in the destination columns?

Yes. The macro writes directly to the two columns to the right of your selection. If those columns have data, that data gets overwritten. Insert two blank columns first if you have something there you want to keep.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running. Because the macro only adds data to the two adjacent columns (it never modifies your source text), undo cleanly removes the count columns. The macro runs entirely offline. Free, hosted on GitHub, source visible.