more tweaking
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
"Ron Coderre" wrote:
Set the Number format to TEXT
Set Data Validation to:
Category: Custom
Formula:
=AND(ISNUMBER(N(A1)),LEN(A1)<=6,ISERROR(FIND("0",A 1)),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
"Ron Coderre" wrote:
Try this:
DataValidation
Allow: Whole Number
Between 111111 and 999999
Does that do what you want?
***********
Regards,
Ron
"John" wrote:
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
|