Search code examples
excelexcel-2013

Simple Excel Data Validation didn't accept typed numbers

I'm working with Excel 2013

I would like to make a custom Data Validation on cell A7 which allows:

  • any numbers with length of 3

or

  • any numbers with length of 6

I know the formula below is not optimized but this is only for testing combination AND OR purpose. While the formula below is accepted by the Data Validation window, when testing, none of the numbers are accepted.

=OR(AND(ISNUMBER(A7);LEN(A7)=3);AND(ISNUMBER(A7);LEN(A7)=6))

The same formula (reformatted for easy reading):

=OR(
    AND(
       ISNUMBER(A7);
       LEN(A7)=3
    );
    AND(
       ISNUMBER(A7);
       LEN(A7)=6
    )
)

Any idea why this formula didn't accept typed numbers?

Thanks.

Solution

  • This error will occur if A7 has been formatted to Text:

    enter image description here