Data Validation

[Guest]

I want to validate a cell that it must have an 8-digit number put in it---no
shorter, no longer. No matter how I validate the cell it won't work unless I
select "text length". This is not to be text! It's supposed to be an
8-digit number! What gives? How does one validate this cell to restrict it
to just that? How can something so simple be so contrary?

 

[Guest]

Try something like this:

Select the cells to have Data Validation, with A1 as the active cell

<Data><Validation><Settings tab>
Allow: Custom
Formula: =AND(A1>0,INT(A1)=A1,LEN(A1)=8)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

So, why doesn't setting it to a Whole Number Between 10000000 and 99999999
work?

 

[Guest]

The number can start with 0. As soon as I put either 8 zeros or 00000001, it
changes it to simply 0, so then a person can put in as small of a number they
want.

 

[Guest]

Can't use an 8-digit number such as 00000001

Try something like this:

Select the cells to have Data Validation, with A1 as the active cell

<Data><Validation><Settings tab>
Allow: Custom
Formula: =AND(A1>0,INT(A1)=A1,LEN(A1)=8)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

It also won't let me type 12345678, which is an 8-digit number. The cell is
4 cells merged, but when you click in it, it is identified as E4 so I changed
all the A1 references in your formula to E4 but it won't accept 12345678.

 

[Guest]

Good point...but, I think it would have to be a Whole Number

Between 9,999,999 and 100,000,000

instead of
Between 99,999,999 and 10,000,000

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

Your problem is how Excel stores numbers (particulary the case with leading
zeros). 01234567 is not an 8-digit number. Even if you apply a custom
format of 00000000, and enter the value as 01234567, the cell value is still
the number 1234567. You have two options depending on whether you want to
allow leading zeros or not. I know that both of these have already been
suggested, but I just thought it might help to clarify the difference between
the two.

I. With leading zeros.
you will HAVE to format the cell as text and use a custom data validation of
=ISNUMBER(--A1)*(LEN(A1)=8)*(--A1>=0)
Usually this is not a problem because you can still do most mathematical
operators with text that contain only digits. You might have to change
formulas like SUM(A1:A10) to SUMPRODUCT(--A1:A10).

II. Do not allow leading zeros.
Allow Whole numbers between 10000000 and 99999999.

 

[Guest]

OK...see if this get you any closer

Set the format of the input cells to Text (so they'll display leading zeros)
<Format><Cells><Number tab><Category: Text>

<Data><Validation><Settings tab>
Allow: custom
Formula is: =AND(--A1>0,INT(A1)=(--A1),LEN(A1)=8)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

I merged 4 cells(E1,F1,G1 & H1) and put Ron's formula into E1 and it worked
OK for me. It rejected alpha strings (abc...) or numbers less 8 digits.

XL2003.

 

[Guest]

Further .... it rejected numbers with leading zero(s)! (which I understand is
what you want).

 

[Guest]

Ron's amended works (for me), allowing leading zeros.

 

[Guest]

This appears to be working in all respects. Thank you very much! Connie

 

[Guest]

Thanks for the feedback, Connie....I'm glad that worked for you.

***********
Regards,
Ron

XL2002, WinXP-Pro