software

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 quot;text lengthquot;. 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?

quot;Connie Martinquot; wrote:

gt; I want to validate a cell that it must have an 8-digit number put in it---no
gt; shorter, no longer. No matter how I validate the cell it won't work unless I
gt; select quot;text lengthquot;. This is not to be text! It's supposed to be an
gt; 8-digit number! What gives? How does one validate this cell to restrict it
gt; 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

lt;Datagt;lt;Validationgt;lt;Settings tabgt;
Allow: Custom
Formula: =AND(A1gt;0,INT(A1)=A1,LEN(A1)=8)

Does that help?

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

XL2002, WinXP-Proquot;Connie Martinquot; wrote:

gt; I want to validate a cell that it must have an 8-digit number put in it---no
gt; shorter, no longer. No matter how I validate the cell it won't work unless I
gt; select quot;text lengthquot;. This is not to be text! It's supposed to be an
gt; 8-digit number! What gives? How does one validate this cell to restrict it
gt; 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.

quot;dlwquot; wrote:

gt; So, why doesn't setting it to a Whole Number Between 10000000 and 99999999
gt; work?
gt;
gt; quot;Connie Martinquot; wrote:
gt;
gt; gt; I want to validate a cell that it must have an 8-digit number put in it---no
gt; gt; shorter, no longer. No matter how I validate the cell it won't work unless I
gt; gt; select quot;text lengthquot;. This is not to be text! It's supposed to be an
gt; gt; 8-digit number! What gives? How does one validate this cell to restrict it
gt; gt; to just that? How can something so simple be so contrary?

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

quot;Ron Coderrequot; wrote:

gt; Try something like this:
gt;
gt; Select the cells to have Data Validation, with A1 as the active cell
gt;
gt; lt;Datagt;lt;Validationgt;lt;Settings tabgt;
gt; Allow: Custom
gt; Formula: =AND(A1gt;0,INT(A1)=A1,LEN(A1)=8)
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Connie Martinquot; wrote:
gt;
gt; gt; I want to validate a cell that it must have an 8-digit number put in it---no
gt; gt; shorter, no longer. No matter how I validate the cell it won't work unless I
gt; gt; select quot;text lengthquot;. This is not to be text! It's supposed to be an
gt; gt; 8-digit number! What gives? How does one validate this cell to restrict it
gt; gt; 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.

quot;Ron Coderrequot; wrote:

gt; Try something like this:
gt;
gt; Select the cells to have Data Validation, with A1 as the active cell
gt;
gt; lt;Datagt;lt;Validationgt;lt;Settings tabgt;
gt; Allow: Custom
gt; Formula: =AND(A1gt;0,INT(A1)=A1,LEN(A1)=8)
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Connie Martinquot; wrote:
gt;
gt; gt; I want to validate a cell that it must have an 8-digit number put in it---no
gt; gt; shorter, no longer. No matter how I validate the cell it won't work unless I
gt; gt; select quot;text lengthquot;. This is not to be text! It's supposed to be an
gt; gt; 8-digit number! What gives? How does one validate this cell to restrict it
gt; gt; 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-Proquot;dlwquot; wrote:

gt; So, why doesn't setting it to a Whole Number Between 10000000 and 99999999
gt; work?
gt;
gt; quot;Connie Martinquot; wrote:
gt;
gt; gt; I want to validate a cell that it must have an 8-digit number put in it---no
gt; gt; shorter, no longer. No matter how I validate the cell it won't work unless I
gt; gt; select quot;text lengthquot;. This is not to be text! It's supposed to be an
gt; gt; 8-digit number! What gives? How does one validate this cell to restrict it
gt; gt; 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)*(--A1gt;=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.
quot;Connie Martinquot; wrote:

gt; I want to validate a cell that it must have an 8-digit number put in it---no
gt; shorter, no longer. No matter how I validate the cell it won't work unless I
gt; select quot;text lengthquot;. This is not to be text! It's supposed to be an
gt; 8-digit number! What gives? How does one validate this cell to restrict it
gt; 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)
lt;Formatgt;lt;Cellsgt;lt;Number tabgt;lt;Category: Textgt;

lt;Datagt;lt;Validationgt;lt;Settings tabgt;
Allow: custom
Formula is: =AND(--A1gt;0,INT(A1)=(--A1),LEN(A1)=8)

Does that help?

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

XL2002, WinXP-Proquot;Connie Martinquot; wrote:

gt; It also won't let me type 12345678, which is an 8-digit number. The cell is
gt; 4 cells merged, but when you click in it, it is identified as E4 so I changed
gt; all the A1 references in your formula to E4 but it won't accept 12345678.
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try something like this:
gt; gt;
gt; gt; Select the cells to have Data Validation, with A1 as the active cell
gt; gt;
gt; gt; lt;Datagt;lt;Validationgt;lt;Settings tabgt;
gt; gt; Allow: Custom
gt; gt; Formula: =AND(A1gt;0,INT(A1)=A1,LEN(A1)=8)
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Connie Martinquot; wrote:
gt; gt;
gt; gt; gt; I want to validate a cell that it must have an 8-digit number put in it---no
gt; gt; gt; shorter, no longer. No matter how I validate the cell it won't work unless I
gt; gt; gt; select quot;text lengthquot;. This is not to be text! It's supposed to be an
gt; gt; gt; 8-digit number! What gives? How does one validate this cell to restrict it
gt; gt; gt; to just that? How can something so simple be so contrary?

I merged 4 cells(E1,F1,G1 amp; 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.

quot;Connie Martinquot; wrote:

gt; It also won't let me type 12345678, which is an 8-digit number. The cell is
gt; 4 cells merged, but when you click in it, it is identified as E4 so I changed
gt; all the A1 references in your formula to E4 but it won't accept 12345678.
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try something like this:
gt; gt;
gt; gt; Select the cells to have Data Validation, with A1 as the active cell
gt; gt;
gt; gt; lt;Datagt;lt;Validationgt;lt;Settings tabgt;
gt; gt; Allow: Custom
gt; gt; Formula: =AND(A1gt;0,INT(A1)=A1,LEN(A1)=8)
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Connie Martinquot; wrote:
gt; gt;
gt; gt; gt; I want to validate a cell that it must have an 8-digit number put in it---no
gt; gt; gt; shorter, no longer. No matter how I validate the cell it won't work unless I
gt; gt; gt; select quot;text lengthquot;. This is not to be text! It's supposed to be an
gt; gt; gt; 8-digit number! What gives? How does one validate this cell to restrict it
gt; gt; gt; to just that? How can something so simple be so contrary?