Allow Only N Digits in Data Validation in Google Sheets (Accept Leading Zeros)

Do you know how to allow only n digits in a cell/cell range in data validation in Google Sheets?

Say you want to restrict the entry of a number in a cell to 10 digits or a maximum of 10 digits.

If you think you can use a custom formula around ISNUMBER and LEN for this, it won’t work in all cases.

I haven’t get it. Can you explain, please?

Assume the cell in question (data validation) is B1. To allow only n (read 10) digits in data validation, you can use the below formula.

=and(isnumber(B1),len(B1)=10)

For a maximum of 10 digits, change =10 to <=10.

To use this formula, open the data validation dialog box (menu command) from Data > Data validation > Criteria > Custom formula is.

Insert the above AND, ISNUMBER, and LEN combo formula in the blank field there.

What if I want to allow leading zeros, such as a phone number starting with 0, with the numbers and limit the number of digits?

When it comes to numbers, leading zeros (0 prefixes) make differences in spreadsheet formulas/rules.

Because, most probably, you may want to change the format from number to text.

So we may require a formula that will only accept the digits from 0 to 9 either in number or text format.

We can use Regexmatch here.

Regexmatch to Allow Only N Digits and Leading Zeros in Data Validation

You may replace n in the following formulas with the number you want. As per our example, replace it with 10

Formula # 1 – Permit Only N digits (with or without leading zeros)

=regexmatch(B1&"","^[0-9]{n}$" )

Formula # 2 – A Maximum of N Digits (with or without leading zeros)

=regexmatch(B1&"","^[0-9]{0,n}$" )

Here are the necessary settings within the data validation dialog box.

Allow Only N Digits in Data Validation - Five Settings

To open the above dialog box, go to the Data menu.

Settings (as per the screenshot above):-

  1. It’s the cell or cell range in which you want to apply the above data validation rules.
  2. Copy-paste either of the above formulas. You may replace cell reference B1 in the formula with the selected cell in point # 1 above.
  3. If you use the first Regexmatch formula, it will allow the user to enter only a number with n digits. If you go ahead with the second one, it will permit the user to enter only a number with a maximum of n digits. Please note that the formulas support numbers formatted as text to extend support to leading 0s.
  4. Show a warning/help text when trying to enter a number that violates the set rule in that cell.

What about a Cell Range?

You want, most probably, to apply the above regex data validation rule to a cell range/array.

That will help you create a proper and valid list in your Google Sheets, such as a list of phone numbers, product codes, employee IDs, etc.

There are no major changes either in the formula or in the data validation settings!

The two changes required are as follows.

As per the above image, in point # 1 within the dialog box, replace Sheet1!B1 with the corresponding cell range.

E.g.:

To allow only n digits in data validation for the range C1:C10 in the “Test Data” sheet, change Sheet1!B1 to 'Test Data'!C1:C10.

In the formula, you should change B1 to C1.

That’s all. Thanks for the stay. Enjoy!

Resources:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.