alpha numeric data validation for excel
"Harlan Grove" wrote in message
...
"T. Valko" wrote...
...
Ooops!
I can't believe that between us we didn't catch this...
Can't use array constants in a DataValidationCustom rule.
OK, should have causght that.
=AND(ABS(CODE(A1)77.5)<13,ABS(CODE(MID(A1,2,1))77.5)<13,
COUNT(1/(MID(A1,3,6)=TEXT(MID(A1,3,5),"00000"))))
Note that the 6 as 3rd arg in the 1st MID call is intentional. It
eliminates the need for a LEN test.
COUNT(1/(MID(A1,3,6)=TEXT(MID(A1,3,5),"00000")))
This seems to work just as well:
MID(A1,3,6)=TEXT(MID(A1,3,5),"00000")

Biff
Microsoft Excel MVP
