# Data validation rule that accepts only 16-digit numbers

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.

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

# TL;DR

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

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}\$")
``````