Data Validation

[John]

I require a Data Validation that will only allow up to 6 digits to be
inserted. This can only allow digits 1 - 9 as any one of the 6 digits.

I've tried Text Length, but that allows characters - / T etc

Any ideas?

Thanks

 

[Guest]

Try this:
Data>Validation
Allow: Whole Number
Between 111111 and 999999

Does that do what you want?

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

 

[Guest]

,If you need 6 digits you data validation > allow: whole number data: between
minimum: 100,000 maximum: 999999. If less than 6 digits use less than
1,000,000
HTH
Sally

 

[Guest]

Set the Number format to TEXT
Set Data Validation to:
Category: Custom
Formula:
=AND(ISNUMBER(N(A1)),LEN(A1)<=6,ISERROR(FIND("0",A1)),ISERROR(FIND(".",A1)),ISERROR(FIND("-",A1)),ISERROR(FIND("+",A1)),ISERROR(FIND("=",A1)))

So far, that checks if:
the input is numeric
The length is 6 or less
There are no zeros, decimal points, plus or minus signs, or equal signs.

Just tack on anything else that violates your rules.

I hope that helps?

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

 

[Guest]

The Data Validation formula should be
=AND(ISNUMBER(--A1),LEN(A1)<=6,ISERROR(FIND("0",A1)),ISERROR(FIND(".",A1)),ISERROR(FIND("-",A1)),ISERROR(FIND("+",A1)),ISERROR(FIND("=",A1)))

Changed ISNUMBER(N(A1)) to ISNUMBER(--A1).
( ISNUMBER(N(A1)) allowed text )

After that, I'm out of ideas?

I hope that helps.

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

 

[John]

Thanks guys

Using Whole numbers between 1 - 999999 does the trick

 

[John]

Thanks Ron, you put a good deal of effort into your reply