Data Validation can stop people typing more than a set number of characters into a cell, or insist on an exact length, such as a 10-digit mobile number or a 6-digit PIN code.
WhereData → Data Validation → Text length
Exact lengthCustom formula with LEN
Works inAll versions
Allow up to 5 characters
- Select the cells → Data → Data Validation.
- Under Allow, choose Text length.
- Set Data to less than or equal to and Maximum to 5. Click OK.
Typing Christopher (11 characters) now shows an error, and the entry is rejected.
Other length rules
| Rule | Settings |
|---|---|
| At least 3 characters | Text length → greater than or equal to → 3 |
| Between 5 and 10 | Text length → between → 5 and 10 |
| Exactly 6 (PIN code) | Text length → equal to → 6 |
Exact length and digits only
Text length counts any characters. For a 10-digit mobile number, choose Allow: Custom and use (for cells starting at A2):
=AND(LEN(A2)=10,ISNUMBER(--A2))--A2 turns the entry into a number, so letters fail the rule. Format the cells as Text first so leading zeros aren't lost. Other examples: PAN =LEN(A2)=10, GSTIN =LEN(A2)=15.
Help people get it right
- Input Message tab: show a hint when the cell is selected, such as Maximum 5 characters.
- Error Alert tab: write your own error text. Choose Warning or Information to allow the entry anyway after a prompt.
- Show the remaining characters next to the cell:
=5-LEN(A2).
Things to know
- Data Validation only checks typing. Pasted values bypass it. Use Data → Data Validation ▾ → Circle Invalid Data to find them.
- Rules apply to new entries; existing long values aren't changed.
- To cut existing text down to 5 characters, use
=LEFT(A2,5).