LEN function
LEN returns the number of characters in a text string, including spaces. It is common for checking field lengths, spotting hidden spaces or counting a character with SUBSTITUTE.
Example
Read it as a sentence: count the characters in F1, which gives 0.
Excel shows 0
F
1
Data
Syntax
LEN(text)
-
text(required) - The text to measure.
Returns: The number of characters, including spaces.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=LEN("Excel") | 5 | Basic |
=LEN("") | 0 | Empty |
=LEN(12.5) | 4 | Number |
=LEN(1/3) | 17 | Long decimal |
=LEN(TRUE) | 4 | Bool |
=LEN(UNICHAR(128512)) | 1 | Emoji (surrogate pair) |
=LEN(Data!F1) | 0 | Blank |
=LEN(Data!F5) | #DIV/0! | Error |
The Data sheet these formulas read
A cell that starts with = holds that formula.
F
1
2
'123
3
2024-02-29
4
TRUE
5
=1/0
Data
Watch out for
- Spaces and invisible characters count.
- Numbers are measured as plain text, ignoring cell formatting.
Where it works
- Excel
- LEN is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Text functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .