Limit Format in Cell to NAANNAA Format Using Data Validation in Excel?

[TechSlinger]

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.

 

[Deniall]

I'd use VBA to write a user-defined function to do the test, returning TRUE or FALSE, then use that function in the conditional format. Much easier to achieve, and also much easier for a later person to understand.

 

[TechSlinger]

Thanks for the input but with 200+ worksheets and 30 cells per worksheet I would much rather come up with data validation rule....plus I do not know how to write a user defined function and I already have a huge amount of code in the on change event.

 

[Deniall]

Either way, cancel my previous suggestion.[&nbsp;] I stupidly read your question as applying to conditional formatting, not data validation.[&nbsp;] I believe that "custom" validation cannot incorporate a user defined function.

 

[TechSlinger]

Okay thanks....anyone else have a suggestion.

 

[fredericofonseca]

easy one.

=AND(LEN(N78)>=7,LEN(N78)<11[highlight #4E9A06],EXACT(N78,UPPER(N78))[/highlight],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")))))

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[strongm]

Erm ... isn't that string a little long for a validation rule?