The LEN function in Excel
LEN counts characters: letters, digits, punctuation and spaces. Use it to check codes have the right length, to find stray spaces, and as a building block in other text formulas.
Syntax
=LEN(text)
| Argument | What it means |
|---|---|
| text | The text or cell to measure. |
Examples
1. Length of a code
D2fx=LEN(A2)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Length | ||
| 2 | SH-4417-BLU | omar.haddad@shopmail.com | 11 |
SH-4417-BLU has 11 characters, dashes included.
2. Spot extra spaces
B2fx=LEN(A2)-LEN(TRIM(A2))
| A | B | |
|---|---|---|
| 1 | Name as typed | Extra spaces |
| 2 | Maya Cohen | 3 |
13 characters as typed, 10 after TRIM: 3 spaces too many, the kind that breaks lookups.
3. How often does a character appear?
D2fx=LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Dashes | ||
| 2 | SH-4417-BLU | omar.haddad@shopmail.com | 2 |
Remove every dash with SUBSTITUTE; the difference in length is the number of dashes.
Common mistakes
Counting a number's digits. LEN counts the stored value, not the formatted one: 3.50 shown with two decimals is stored as 3.5 (length 3).
Fix: Use
=LEN(TEXT(B2,"0.00"))if the displayed form matters.
Frequently asked questions
Does LEN count spaces?
Yes, every space counts, including leading and trailing ones. That's what makes it useful for finding them.
Related functions
- TRIMTRIM removes extra spaces from text: at the start, at the end, and doubles between words.
- LEFTLEFT returns the first characters of a text, RIGHT the last ones.
- MIDMID returns characters from the middle of a text: a start position and a length.
- SUBSTITUTESUBSTITUTE replaces specific text inside a cell with other text, everywhere or only one occurrence.