View Single Post
  #23   Report Post  
Old November 30th 08, 04:28 AM posted to microsoft.public.excel.worksheet.functions
T. Valko T. Valko is offline
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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