LEN counts the characters in a cell, including spaces.
Basic use
=LEN(A2)
For "Excel tips" it returns 10, because the space counts.
Count a specific character
To count how many times a letter or symbol appears in a cell:
=LEN(A2)-LEN(SUBSTITUTE(A2, "a", ""))
This removes every "a" and compares the lengths. For case-insensitive counting, wrap A2 in LOWER.
Count words in a cell
=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2), " ", ""))+1
Count characters in a whole range
=SUMPRODUCT(LEN(A2:A20))
Check text length rules
- Flag codes that are not 10 characters:
=LEN(A2)<>10
- Use it in Data Validation to limit how many characters can be typed.
Tip
If LEN gives a larger number than you expect, there are hidden spaces. Clean the cell with TRIM.
Worked example
=LEN(A2) for "Excel 101" returns 9, because spaces count. To count how many times "a" appears: =LEN(A2)-LEN(SUBSTITUTE(A2,"a","")).
Common mistakes
- Forgetting spaces count as characters.
- Seeing a wrong count because of hidden spaces. Check with TRIM.
- Using LEN on numbers and expecting the formatted display, such as dates.
Frequently asked questions
Does LEN count spaces?
Yes, all of them.
How do I count words?
=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1.
Can I limit text to a length?
Yes, use Data Validation with a text length rule.
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: LEN, SUBSTITUTE, SUMPRODUCT.
Start Excel practice → Case studies
