TechSlinger
Technical User
Hello
I am using Excel 2010 and I'm trying to find a way to limit entries via data validation function. The code in the cells are between 7 and 10 characters with the format of NAANNAA for the first 7 characters and the last 3 could be numbers or letters. The validation for this is cells N78 to N108 and these cells are also allowed to be blank.
I posted this on another site and though they provided me with an answer (below), it isn't working correctly because it allows me to enter 1Gz31caND which should be 1GZ31CAND. The post is now closed so I can't ask follow up questions.
=AND(LEN(N78)>=7,LEN(N78)<11,IF(MID("0AA00AA",ROW($1:$7),1)="A",(CODE(MID(N78,ROW($1:$7),1))>64)*(CODE(MID(N78,ROW($1:$7),1))<91),ISNUMBER(-MID(N78,ROW($1:$7),1))),NOT(SUM(0+ISERROR(FIND(MID(N78,ROW($8:$10),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))))
Thanks for any assistance.
EDIT: any of the alpha characters must also be capital letters. Thanks.
I am using Excel 2010 and I'm trying to find a way to limit entries via data validation function. The code in the cells are between 7 and 10 characters with the format of NAANNAA for the first 7 characters and the last 3 could be numbers or letters. The validation for this is cells N78 to N108 and these cells are also allowed to be blank.
I posted this on another site and though they provided me with an answer (below), it isn't working correctly because it allows me to enter 1Gz31caND which should be 1GZ31CAND. The post is now closed so I can't ask follow up questions.
=AND(LEN(N78)>=7,LEN(N78)<11,IF(MID("0AA00AA",ROW($1:$7),1)="A",(CODE(MID(N78,ROW($1:$7),1))>64)*(CODE(MID(N78,ROW($1:$7),1))<91),ISNUMBER(-MID(N78,ROW($1:$7),1))),NOT(SUM(0+ISERROR(FIND(MID(N78,ROW($8:$10),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))))
Thanks for any assistance.
EDIT: any of the alpha characters must also be capital letters. Thanks.