Do you want to restrict the number of digits in a cell/cell range while using data validation in Google Sheets? Well, you might think that using a custom formula with ISNUMBER and LEN would do the trick, but it might not work in all cases. Let me explain.
Assuming the cell you want to validate is B1, you can use the following formula to allow only n (in this case, 10) digits in data validation:
=and(isnumber(B1),len(B1)=10)
If you want a maximum of 10 digits, simply change =10 to <=10 in the formula.
To use this formula, follow these steps:
- Open the data validation dialog box by navigating to Data > Data validation > Criteria > Custom formula is.
- Insert the above formula in the blank field provided (AND, ISNUMBER, and LEN combo).
But what if you want to allow leading zeros, such as a phone number starting with 0, while still limiting the number of digits?
When it comes to numbers, leading zeros (0 prefixes) can make a difference in spreadsheet formulas and rules. In this case, you might want to change the cell format from number to text, which requires a formula that accepts digits from 0 to 9 in either number or text format.
You can achieve this using Regexmatch. Here are two formulas that you can use, replacing n with the desired number (in our example, 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}$")
To apply these formulas, follow the same steps mentioned earlier in the data validation dialog box.
If you want to apply the above regex data validation rules to a cell range or array, such as a list of phone numbers, product codes, or employee IDs, you can do that too. The process remains the same, with just a couple of changes:
- Replace Sheet1!B1 in the data validation dialog box with the corresponding cell range.
- Replace B1 with the appropriate cell reference in the formula.
For example, 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. And in the formula, change B1 to C1.
That’s it! Now you can easily restrict the number of digits in data validation in Google Sheets and ensure that your data meets your requirements.
For more helpful resources related to Google Sheets and data validation, check out Crawlan.com.
Remember, it’s always important to validate your data and ensure that it complies with your specific needs.
