Catch entries that are too long or too short — a code that should be 5 characters, a tweet over the limit. A LEN rule flags cells whose length is off.
<> 5 highlights anything that isn’t the required length.
The example
Codes that aren’t 5 characters are flagged.
| A | B | |
|---|---|---|
| 1 | Code | Length |
| 2 | AB123 | 5 |
| 3 | AB12 | 4 |
The formula
Flag the wrong lengths:
How it works
LEN measures, the comparison flags:
LEN(cell)returns the number of characters (spaces included).- Compare it:
<> 5for “wrong length,”> 100for too long,< 3for too short. - Apply as a CF rule so problem entries jump out for cleanup.
- Pair with TRIM in the test —
=LEN(TRIM(A1))<>5— if stray spaces shouldn’t count toward the length.
Prevent it on entry with Data Validation → Text length. CF highlights existing wrong-length data; validation stops new bad entries — see the restrict-input-length recipe.
Try it: interactive demo
Required length, then entries.
Variations
Too long only
Over a limit:
Ignore spaces
Trim first:
Block on entry
Data Validation → Text length.
Pitfalls & errors
Spaces count. LEN includes trailing spaces — “ABC ” is length 4. Use TRIM if that’s not intended.
Numbers vs text length. LEN counts the displayed digits of a number, which can surprise with decimals or formatting.
Match the relative ref to the active cell.
Practice workbook
Frequently asked questions
How do I highlight cells that are the wrong length in Excel?
How do I ignore trailing spaces?
How do I stop wrong-length entries being typed?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class