What it does
LEN counts the number of characters in a text value.
Syntax or pattern
=LEN(text)✅
5 practical examples
1
Count characters in a cell
Return the length of a product code.
=LEN(A2)Includes spaces.
2
Find long entries
Flag names longer than 30 characters.
=IF(LEN(A2)>30,"Too long","OK")Useful for data validation checks.
3
Count characters without spaces
Remove spaces before counting.
=LEN(SUBSTITUTE(A2," ",""))Useful when spaces should not count.
4
Find blank-looking cells
Check cells with hidden spaces.
=LEN(A2)A result greater than 0 means something is there.
5
Validate code length
Check that an ID has exactly 8 characters.
=IF(LEN(A2)=8,"OK","Check")Good for imported IDs.
Common mistakes to avoid
- Not handling missing delimiters.
- Forgetting that some text functions return text, not numbers.
- Using fixed positions when the text layout is not consistent.
Related Excel examples
FAQ
Can text formulas work with Excel Tables?
Yes. Table references can make text-cleaning formulas easier to understand.