View Single Post
  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default alpha numeric data validation for excel

"T. Valko" wrote...
....
This seems to work:

=SUM(2-LEN(SUBSTITUTE(LEFT(A2,2),CHAR(ROW(INDIRECT("65:90 "))),"")),
--(COUNT(-MID(A2,ROW(INDIRECT("3:7")),1))=5),--(LEN(A2)=7))=4

....

Or avoiding the volatile INDIRECT function but taking advantage of
ASCII encoding,

=AND(LEN(x)=7,ABS(CODE(MID(x,{1;2},1))-77.5)<13,COUNT(-MID(x,
{3;4;5;6;7},1))=5)