View Single Post
  #21   Report Post  
Old November 29th 08, 06:33 PM 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



--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
"Harlan Grove" wrote in message
...
"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)


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 DataValidationCustom rule.


--
Biff
Microsoft Excel MVP