data validation problem

[PayeDoc]

Hello All

I need to set validation for a column that will only allow any 6 digit
entry - so the following must be OK:
123456
001122
012233
etc
Setting text length=6 doesn't work, because it rejects 012233 etc. How do I
do this?

Hope someone can help.
Many thanks
Leslie Isaacs

 

[Roger Govier]

Hi

Format the column >Custom>000000
Use the following formula for your DV
=AND(ISNUMBER(A1),LEN(SUBSTITUTE(A1,"0","1")=6))

 

[PayeDoc]

Hello Roger

Many thanks for your reply.
I can see how to use your DV formula for one cell, but how do I use it for
en entire column (apart from rows 1 to 3, which have heading info!)?

Sorry to be so dim!
Les

 

[Student]

I don't think this formula will work because len works on text. It
won't return an error but the count will be wrong.

As you suggested the column should be formatted as 000000, but I would
use formula
=LEN(TEXT(a1,"000000"))=6 for data validation.

As for OP's question on validating all cells in the range, select the
entire range, then enter the formula in DV for the first cell in the
range. Excel will take care of the rest, applying the same rule for
other cells in the range.

 

[Student]

So the complete formula would be

=AND(ISNUMBER(a1),LEN(TEXT(a1,"000000"))=6)

 

[PayeDoc]

Hello 'Student'

Thanks for your suggestion.
I have formatted the cells as 000000 and used your formula for the DV (and
extended the range as you suggested), which does now allow 001122, and in
fact amends 1122 to 001122 which is fine. The only problem is that it allows
alphas - e.g. aabbcc, 1122cc, etc - but not aabb) which it must not.
Presumably this means the formatting as 000000 isn't right?
Sorry to be a pain!

Thanks for your continued help
Les




I don't think this formula will work because len works on text. It
won't return an error but the count will be wrong.

As you suggested the column should be formatted as 000000, but I would
use formula
=LEN(TEXT(a1,"000000"))=6 for data validation.

As for OP's question on validating all cells in the range, select the
entire range, then enter the formula in DV for the first cell in the
range. Excel will take care of the rest, applying the same rule for
other cells in the range.

 

[Student]

The complete formula is:

=AND(ISNUMBER(a1),LEN(TEXT(a1,"000000"))=6)

 

[PayeDoc]

'Student'

Got it!!
Many thanks for your help: not only do I have the solution - I've also
learnt something!!

Thanks again
Les


The complete formula is:

=AND(ISNUMBER(a1),LEN(TEXT(a1,"000000"))=6)