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?

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

"Connie Martin" wrote:

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?

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(A10,INT(A1)=A1,LEN(A1)=8)

Does that help?

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

XL2002, WinXP-Pro


"Connie Martin" wrote:

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?

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.

"dlw" wrote:

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

"Connie Martin" wrote:

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?

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

"Ron Coderre" wrote:

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(A10,INT(A1)=A1,LEN(A1)=8)

Does that help?

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

XL2002, WinXP-Pro


"Connie Martin" wrote:

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?

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.

"Ron Coderre" wrote:

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(A10,INT(A1)=A1,LEN(A1)=8)

Does that help?

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

XL2002, WinXP-Pro


"Connie Martin" wrote:

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?

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


"dlw" wrote:

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

"Connie Martin" wrote:

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?

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.



"Connie Martin" wrote:

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?

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(--A10,INT(A1)=(--A1),LEN(A1)=8)

Does that help?

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

XL2002, WinXP-Pro


"Connie Martin" wrote:

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.

"Ron Coderre" wrote:

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(A10,INT(A1)=A1,LEN(A1)=8)

Does that help?

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

XL2002, WinXP-Pro


"Connie Martin" wrote:

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?

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.

"Connie Martin" wrote:

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.

"Ron Coderre" wrote:

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(A10,INT(A1)=A1,LEN(A1)=8)

Does that help?

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

XL2002, WinXP-Pro


"Connie Martin" wrote:

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?

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

"Toppers" wrote:

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.

"Connie Martin" wrote:

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.

"Ron Coderre" wrote:

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(A10,INT(A1)=A1,LEN(A1)=8)

Does that help?

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

XL2002, WinXP-Pro


"Connie Martin" wrote:

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?

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

"Ron Coderre" wrote:

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(--A10,INT(A1)=(--A1),LEN(A1)=8)

Does that help?

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

XL2002, WinXP-Pro


"Connie Martin" wrote:

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.

"Ron Coderre" wrote:

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(A10,INT(A1)=A1,LEN(A1)=8)

Does that help?

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

XL2002, WinXP-Pro


"Connie Martin" wrote:

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?

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

"Ron Coderre" wrote:

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(--A10,INT(A1)=(--A1),LEN(A1)=8)

Does that help?

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

XL2002, WinXP-Pro


"Connie Martin" wrote:

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.

"Ron Coderre" wrote:

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(A10,INT(A1)=A1,LEN(A1)=8)

Does that help?

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

XL2002, WinXP-Pro


"Connie Martin" wrote:

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?

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

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

XL2002, WinXP-Pro


"Connie Martin" wrote:

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

"Ron Coderre" wrote:

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(--A10,INT(A1)=(--A1),LEN(A1)=8)

Does that help?

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

XL2002, WinXP-Pro


"Connie Martin" wrote:

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.

"Ron Coderre" wrote:

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(A10,INT(A1)=A1,LEN(A1)=8)

Does that help?

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

XL2002, WinXP-Pro


"Connie Martin" wrote:

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?