Do you want to allow only N digits in data validation in Google Sheets, including values with leading zeros?

For example, you may want to restrict users to entering exactly 10 digits (e.g., a phone number) or up to 10 digits in a cell.

If you’re thinking of using a custom formula based on ISNUMBER and LEN, think again—it doesn’t cover all scenarios.

Let’s look at why and what works better.

Why Common Approaches May Fail

Let’s say you’re applying data validation to cell B1. A typical formula to allow exactly 10 digits looks like this:

=AND(ISNUMBER(B1), LEN(B1) = 10)

To allow up to 10 digits, simply change =10 to <=10.

While this formula works with regular numbers, it fails when leading zeros are involved—for example, a phone number like 0123456789.

That’s because Google Sheets automatically strips leading zeros from numeric values. So even if a user enters 0123456789, Sheets stores it as 123456789, which has only 9 digits.

To preserve leading zeros, you have two options:

• Format the cell as Plain text via Format > Number > Plain text, or
• Apply a Custom number format (e.g., 0000000000) via Format > Number > Custom number format

However, here’s the catch:

• If you use custom number formatting, the above ISNUMBER + LEN formula works because the stored value remains numeric.
• If you format the cell as text, the formula fails because ISNUMBER returns FALSE, even if the content is all digits.

So how do you allow only N digits in data validation in Google Sheets, especially when leading zeros matter and the input might be text?

Allow Only N Digits in Data Validation Using REGEXMATCH

To handle both numeric and text inputs—including those with leading zeros—you can use the REGEXMATCH function in your data validation rule.

Formula #1 – Exactly N Digits (e.g., 10)

=AND(LEN(B1)=10, REGEXMATCH(B1 & "", "^\d+$"))

Formula #2 – Up to N Digits (e.g., max 10)

=AND(LEN(B1)<=10, REGEXMATCH(B1 & "", "^\d+$"))

This approach works even when the cell is formatted as text because it checks:

• That the value has the desired number of characters.
• That all characters are digits (0–9), including any leading zeros.

To Apply This in Data Validation:

1. Select the cell or range.
2. Go to Data > Data validation.
3. Click + Add rule.
4. Under Criteria, choose Custom formula is.
5. Paste one of the formulas above.
6. Check Reject the input.
7. Optionally, add a help text to guide users.

How These Formulas Work

• LEN(B1) checks the total number of characters in the cell.
• REGEXMATCH(B1 & "", "^\d+$") ensures all characters are digits (0–9). The & "" ensures that even numbers formatted as text are treated correctly.

By combining these two checks, you ensure the input is purely numeric and has the correct number of digits—even if it starts with one or more zeros.

Key Data Validation Settings for N-Digit Inputs in Google Sheets

When applying data validation in Google Sheets, keep these tips in mind:

• Apply to range: Choose the cell or range where the rule should apply (e.g., C1:C10).
• Use custom formula: Insert one of the REGEXMATCH formulas shown above.
• Adjust for ranges: Update the cell reference in the formula to match the first cell in your selection (e.g., replace B1 with C1).
• Support for text format: These formulas work whether the input is numeric or formatted as text—making them ideal for data with leading zeros.
• Input help text: Provide a helpful message so users know what input is expected (e.g., “Please enter exactly 10 digits.”)

Conclusion

If you’re looking to allow only N digits in data validation in Google Sheets, especially while supporting leading zeros, REGEXMATCH combined with LEN offers the most flexible and reliable solution.

Whether you’re validating phone numbers, employee IDs, or ZIP codes, this method ensures clean, consistent, and properly formatted input—exactly the way you need it.

Related Resources

• Restrict Entering Special Characters in Google Sheets (Data Validation)
• The Best Data Validation Examples in Google Sheets
• Check Time Input in Google Sheets (Formula to Validate Time Cells)
• ISURL Function in Google Sheets: Check and Validate URLs
• How to Validate Alphanumeric Data in Google Sheets
• Reject a List of Items in Data Validation in Google Sheets
• Data Validation to Enter Values from a List as per the Order in the List in Google Sheets
• Validate Comma Separated Numbers within Specific Range – Google Sheets
• Limit Sales Qty Within the Stock Qty in Google Sheets (Data Validation)