Restrict Text Length on Entry

All versionsData Validation

Stop bad data at the door. Data Validation with a text-length rule blocks entries that are too long or too short — perfect for fixed-width codes, ZIPs, or character limits.

Quick formula: Data → Data Validation → Allow: Text length:

Allow: Text length Data: equal to Length: 5

Excel rejects anything that isn’t exactly 5 characters. Choose between, less than, or greater than for other limits.

Functions used (tap for the full reference guide):

LEN

The example

A 5-character product code field that rejects wrong lengths.

	A	B
1	Entry	Accepted?
2	AB123	Yes (5)
3	AB12	No (4)
4	AB1234	No (6)

The formula

Set up the rule (no cell formula needed for the built-in option):

Data Validation → Allow: Text length Data: equal to Length: 5 // or use a custom formula for more control

How it works

The built-in text-length rule is the quickest path:

Select the cells, then Data → Data Validation. Under Allow, choose Text length.
Pick the comparison — equal to, between, less than or equal to — and enter the length(s).
Add an Input Message to hint the rule, and an Error Alert to explain rejections.
For combined rules (e.g. exactly 5 chars and all digits), switch Allow to Custom and use a formula like =AND(LEN(A1)=5, ISNUMBER(A1*1)).

Validation only checks typed entries. It does not catch values pasted in or already present. To audit an existing column, add a helper formula =LEN(A2) or Data → Data Validation → Circle Invalid Data.

Try it: interactive demo

Live demo

Set a required length; type to test.

Required length

Entry

Accepted:

Variations

Custom: length + digits

Allow: Custom formula:

=AND(LEN(A1)=5, ISNUMBER(A1*1))

Up to N characters

Cap a comment field:

Text length → less than or equal to → 100

Audit existing entries

Helper column to find bad lengths:

=IF(LEN(A2)<>5, "Fix", "")

Pitfalls & errors

Paste bypasses validation. Pasting values can skip the rule entirely — validation only fires on manual entry. Use Circle Invalid Data to find offenders.

Spaces count as length. A trailing space makes “AB12 ” pass a length-5 rule. Combine with TRIM in a custom formula if that matters.

Numbers vs text length. “Text length” counts characters even for numeric entries; for value ranges use the Whole Number or Decimal option instead.

Practice workbook

📊

Download the free Restrict Text Length on Entry practice workbook

A field with a live text-length validation rule, the custom and audit variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I limit text length in an Excel cell?

Select the cells, go to Data → Data Validation, set Allow to Text length, and choose equal to / between / less than with your limit. Excel rejects entries that don't fit.

How do I require an exact length and digits only?

Use Allow: Custom with a formula like =AND(LEN(A1)=5, ISNUMBER(A1*1)) to require exactly 5 numeric characters.

Why didn't validation catch a pasted value?

Data Validation only checks values typed directly. Pasted or pre-existing values slip through; use Circle Invalid Data to find them.

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

Function references: LEN