The Excel LEN function counts the characters in a text string — every letter, digit, punctuation mark, and yes, every space, visible or not. On its own it validates field lengths and finds the trailing spaces that break lookups; inside formulas it is the silent arithmetic engine behind RIGHT + LEN suffix extracts and the famous LEN−SUBSTITUTE counting trick.
=LEN(TRIM(A2)).
Syntax
| Argument | Description | |
|---|---|---|
text | Required | The text string (or cell reference) whose characters you want counted. Spaces count. A truly empty cell returns 0. |
Available in: every version of Excel. Numbers are counted as their underlying stored value, not their formatted display — a cell showing $1,234.00 has LEN 4, because the stored value is 1234.
Count characters in a cell
Drafting posts with a character limit, validating that part numbers are exactly 11 characters, sizing database fields — one formula does it:
| A | B | |
|---|---|---|
| 1 | Tweet draft | Length |
| 2 | Free Excel class Saturday! | 26 |
| 3 | DFW Excel Experts | 17 |
| 4 | OK | 2 |
Flag rows that break a length rule with IF:
Try it: interactive LEN demo
Type anything — try adding spaces at the end and watch LEN count what you can’t see. Spaces are shown as · in the breakdown.
LEN inside bigger formulas
LEN’s best work happens inside other formulas. The suffix extract — everything after the first hyphen — needs LEN to compute how many characters that is:
The legendary LEN−SUBSTITUTE trick counts occurrences of a character: remove it, measure the shrinkage. Count the hyphens in A2:
Add 1 and (for space as delimiter) you have a word counter:
And the data-cleaning classic — expose invisible padding by comparing raw and trimmed lengths:
Counting cells instead of characters? That’s COUNTA / COUNTIF territory. LEN counts characters inside one string.
Errors & common pitfalls
Pitfall: hidden spaces inflate the count. "Dallas " (trailing space) is 7 characters, not 6 — and it will fail an exact lookup against "Dallas". This is the #1 cause of "LEN says 7 but I only see 6." Diagnose with =LEN(A2) - LEN(TRIM(A2)) and clean with TRIM.
Pitfall: the web’s favorite space isn’t a space. Text pasted from web pages often contains non-breaking spaces (character 160), which TRIM will not remove. Swap them first: =TRIM(SUBSTITUTE(A2, CHAR(160), " ")).
Pitfall: LEN sees stored values, not formatting. A date showing 6/11/2026 returns LEN 5 — the serial number 46184 underneath. Currency symbols, thousands separators, and trailing display zeros aren’t counted either. To measure what’s displayed, measure TEXT: =LEN(TEXT(A2, "m/d/yyyy")).
Pitfall: "empty-looking" cells that aren’t. LEN 0 means truly empty or a formula returning "". If a blank-looking cell shows LEN 1 or more, something invisible lives there — usually a space or apostrophe-entered text.
Practice workbook
Frequently asked questions
Does LEN count spaces?
=LEN("a b") is 3. That makes LEN the standard diagnostic for invisible padding: if LEN(A2) beats LEN(TRIM(A2)), stray spaces are hiding in the cell.How do I count how many times a character appears?
=LEN(A2) - LEN(SUBSTITUTE(A2, "-", "")) counts hyphens. Remove the character, and the length difference is the count.How do I count words in a cell?
=LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2), " ", "")) + 1. TRIM collapses doubled spaces first so they don’t inflate the answer. (Guard empty cells with IF(A2="", 0, …).)Why does LEN on a date return 5?
=LEN(TEXT(A2, "m/d/yyyy")).Why is LEN bigger than the characters I can see?
LEN(TRIM(A2)), and reach for SUBSTITUTE(A2, CHAR(160), " ") when TRIM alone doesn’t close the gap.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class