0

In Google Sheets I am trying to create a custom data validation formula for a column.

I only want 16 character values made up entirely of numbers (e.g. 0-9).

I tried using a formula that works for me in Excel but it didn't work in Sheets:

=AND(LEN(A2)=16,INT(A2)=A2)

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.

Can someone please help me?

2 Answers 2

0

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}"))

0

TL;DR

# Three Approaches
=AND(LEN(A2)=16,INT(A2)=A2*1)
=AND(LEN(A2)=16,ISNUMBER(A2*1))
=REGEXMATCH(A2,"^\d{16}$")

Your Values are Text

Based on the behavior you are describing, Sheets is automatically storing the 16-digit numbers you are entering as text strings, not numbers. For this reason the solutions below are targeted at your specific use case.

INT

Problem

When you apply INT to a number stored as a string, Sheets coerces it to a number. That is why you are experiencing INT(n)<>n:

# Text            # Number 
=INT("10")="10"   =INT(10)=10
=10="10"          =10=10
=FALSE            =TRUE

Solution

Since INT is coercing your string to a number, you must mimic that behavior by trying to coerce the value to a number. In this way the values remain consistent (coercion succeeds or fails together). One technique is to use the value in a simple equation such as n*1:

# Text             # Number 
=INT("10")="10"*1  =INT(10)=10*1
=10=10             =10=10
=TRUE              =TRUE

ISNUMBER

Problem

A number stored as a string will not be coerced to a number by ISNUMBER which is why you are seeing ISNUMBER(n)=FALSE:

# Text            # Number 
=ISNUMBER("10")   =ISNUMBER(10)
=FALSE            =TRUE

Solution

If you want ISNUMBER to see number-like strings as numbers, they must first be coerced to a number. The same n*1 technique can be used:

# Text             # Number 
=ISNUMBER("10"*1)  =ISNUMBER(10*1)
=TRUE              =TRUE

REGEXMATCH

A different aporoach is to use REGEXMATCH with a regular expression that matches when the full string is a 16-digit number: ^\d{16}$. The caret ^ and the dollar $ mark the start and the end of a string for a complete match. This is the way to avoid matching 16 digits of a greater-than-16-character string.

=REGEXMATCH(""&A2,"^\d{16}$")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.