alpha numeric data validation for excel

[T. Valko]

--
Biff
Microsoft Excel MVP

Nice one, Harlan.

I should've realized we could use array constants rather than
ROW(INDIRECT(...)) since it's only a few characters.

Ooops!

I can't believe that between us we didn't catch this...

Can't use array constants in a Data>Validation>Custom rule.

 

[Harlan Grove]

Ooops!

I can't believe that between us we didn't catch this...

Can't use array constants in a Data>Validation>Custom 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.

 

[T. Valko]

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