Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
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? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
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? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
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? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
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? |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
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? |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Data Validation | Excel Worksheet Functions | |||
data validation lists | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |