Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

TechSlinger

Technical User
Oct 31, 2013
19
CA
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'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.
 
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.
 
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.
 
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

FAQ219-2884
FAQ181-2886
 
Erm ... isn't that string a little long for a validation rule?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top