Google-sheets – Data validation with specific condition

formulasgoogle sheetsgoogle-sheets-data-validationmicrosoft excel

I have special conditions for column in Google Sheets which are:

  • should only be filled with numbers
  • no special characters allowed
  • no alphabets allowed
  • no space allowed
  • should be 16 digits in total

I tried using =AND(LEN(A2)=16,INT(A2)=A2) which works in excel, but strangely not in google sheets.
Already tried =AND(LEN(A2)=16,ISNUMBER(A2)) and change the cell format into Number (instead of General) to avoid 1.23457E+15 as an output.
But the 16th digits always change into 0 in excel.

Can someone please help me?

Best Answer

Set the format of the input range to Plain Text. Then set a custom formula validation rule for the range like this:

=LEN(A2)=LEN(REGEXEXTRACT(A2,"\d{16}"))

Related Topic