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!

Register Log in
  • Congratulations SkipVought 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

TechSlinger

Technical User
Oct 31, 2013
19
0
0
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.
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Erm ... isn't that string a little long for a validation rule?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
Force Data Entry Format in Excel
Replies
10
Views
141
combo
combo
duytoi
  • Locked
  • Question
How to add a head to the search results in Excel 2016 using VBA
Replies
2
Views
488
Andrzejek
Andrzejek
bhujanga
  • Locked
  • Question
Prevent Converstion of Long Numeric Strings to Exponential Format 1
Replies
4
Views
127
remeng
remeng
techie131
  • Locked
  • Question
Excel Date format
2
Replies
20
Views
215
SkipVought
S
Andyri
  • Locked
  • Question
Limits in Excel 2
Replies
7
Views
96
Andyri
Andyri

Part and Inventory Search

Sponsor

Back
Top