Excel LEN Function: Count the Characters in a Cell
Returns how many characters a value contains, spaces included.
LEN counts characters. That sounds too simple to need a page, and on its own it is — the reason it matters is what it lets you see. Spaces are invisible on screen but LEN counts them, which makes it the standard way of proving that two cells that look identical are not.
The diagnostic is worth memorising. If a VLOOKUP is returning #N/A on a value you can see in the table, put =LEN() next to both cells. When one says 12 and the other says 13, you have found your trailing space, and TRIM will fix it.
Beyond that, LEN validates fixed-length data — postcodes, account numbers, product codes that should always be eleven characters — and pairs with SUBSTITUTE for the counting trick in the tips below.
Syntax
=LEN(text)Arguments
textRequired- The value to measure. Numbers are counted as the characters they display, and an empty cell returns 0.
The example data
Headers in row 1, data in A2:C5. Note the stray spaces in A3 and A5.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Code | |
| 2 | Alice Moreau | alice@northwind.com | NW-2024-001 |
| 3 | bruno santos | bruno@southgate.co.uk | SG-2024-014 |
| 4 | CHEN WEI | chen@northwind.com | NW-2023-207 |
| 5 | Dana Okafor | dana@eastvale.org | EV-2024-092 |
Worked examples
=LEN(A2)Result: 12
Alice Moreau — eleven letters and one space.
=LEN(A3)Result: 17
The same kind of name, but padded. Thirteen visible characters plus four stray spaces.
=LEN(TRIM(A3))Result: 13
TRIM removes the padding first, so this is the length that actually matters.
=LEN(C2)=11Result: TRUE
Validation. Every code should be eleven characters, so anything returning FALSE is malformed.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
Counts more than you expect
Why it happens: Non-breaking spaces from a web or PDF copy. TRIM does not remove them, because they are character 160 rather than character 32.
How to fix it: Strip them first: =LEN(TRIM(SUBSTITUTE(A2, CHAR(160), " "))).
Returns 0 on a cell that looks filled
Why it happens: The cell holds a formula returning "", which is genuinely zero characters.
How to fix it: That is correct behaviour. Use ISBLANK to distinguish a truly empty cell from an empty string.
Number lengths look wrong
Why it happens: LEN measures the underlying value, not the formatting. A cell showing £1,240.00 holds 1240, so LEN returns 4.
How to fix it: Wrap it in TEXT to measure what is displayed: =LEN(TEXT(D2, "#,##0.00")).
Tips worth knowing
- The go-to duplicate-hunting diagnostic: =LEN(A2)=LEN(TRIM(A2)) returns FALSE wherever a cell has stray spaces.
- Count how many times a character appears with =LEN(A2)-LEN(SUBSTITUTE(A2, "-", "")) — the length lost is the number of hyphens.
- Count words with the same trick on spaces, adding one: =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2), " ", ""))+1.
- LENB exists for double-byte character sets; on Latin text it returns the same as LEN.
Frequently asked questions
Does LEN count spaces?
Yes, every one of them, including leading and trailing spaces. That is exactly what makes it useful for finding the invisible padding that breaks lookups and duplicate checks.
How do I count how many times a character appears in a cell?
Measure the string, remove the character with SUBSTITUTE, measure again, and subtract: =LEN(A2)-LEN(SUBSTITUTE(A2, "-", "")). The difference is how many hyphens there were.
Why does LEN return 4 for a cell showing £1,240.00?
LEN measures the stored value, which is the number 1240. The currency symbol, comma and decimals are formatting, not characters in the cell. Use TEXT() first if you need the length of what is displayed.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.