How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Sunday

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

Posted by   on

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:

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!