Text Manipulationintermediate
Extract a substring from the middle with MID
=MID(A2,5,3)
Excel how-to guide
How to len count characters in Excel
Validate that product codes are exactly 8 characters, flag descriptions that exceed a length limit, or calculate how many characters to extract in a MID formula.
Step-by-step instructions
How to len count characters — 3 steps
1
Reference the cell whose character count you need.
2
LEN returns an integer representing the total number of characters including spaces.
3
Combine with IF to flag cells that do not meet a length requirement.
Example data
Worked example
| Product Code | Length | Valid? |
|---|---|---|
| PROD-0012 | 9 | =IF(LEN(A2)=9,"Yes","No") |
| PROD-12 | 7 | No |
| PROD-00120 | 10 | No |
Common mistakes
Errors to watch out for
LEN counts spaces
If your cells have leading or trailing spaces, LEN will include them. Wrap with TRIM first — LEN(TRIM(A2)) — to count only meaningful characters.
Returns a number, not the text
LEN always returns a numeric count. If you need the text back for display, this is expected — use LEN only for logic and calculations.
FAQ
Frequently asked questions
How do I count only letters, not spaces?
Use LEN(SUBSTITUTE(A2," ","")) to strip spaces before counting. For a true letter-only count, you would need a more complex approach with SUMPRODUCT.
Describe your exact spreadsheet problem — get the formula in seconds.
Open AI Formula Generator — FreeRelated how-to guides
Continue learning.
Data Cleaningbeginner
Remove extra spaces from text with TRIM
=TRIM(A2)