Data Validation
 

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

Data Validation
 

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

Data Validation
 

,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
"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

My prev doesn't work...maybe this
 

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

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

Data Validation
 

Thanks guys

Using Whole numbers between 1 - 999999 does the trick



"Sally" wrote in message
...
,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
"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

more tweaking
 

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


"Ron Coderre" wrote in message
...
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