Forum Discussion
Data Validation with AND and OR
Hi All,
I would like to use data validation on a cell to ensure that it is either an "A" followed by 8 numbers OR starts with "A R".
I can do just the latter with =EXACT(LEFT(I5,3),"A R").
I can do an "A" with length of 9 with =OR(EXACT(LEFT(I5,3),"A"),LEN(I4)=9) but I cannot do a numeric check on the eight digits following the "A" AND I cannot combine both in an OR.
Would appreciate any assistance - thanks much!
Try Below formula
=OR(EXACT(LEFT(I5,3),"A R"),AND(EXACT(LEFT(I5,1),"A"),LEN(RIGHT(I5,LEN(I5)-1))=9,ISNUMBER(RIGHT(I5,LEN(I5)-1)*1)))
I am using a different language version of Excel if this doesn't work please rewrite the formulas I gave on my first post seperately to be sure they run correctly and then combine them.
8 Replies
First codition starts with A, followed by 9 digit number:
=EXACT(LEFT(I5;1),"A")
=LEN(RIGHT(I5,LEN(RIGHT(I5)-1))=9
=ISNUMBER(RIGHT(I5,LEN(RIGHT(I5)-1)*1)
Combine them all wiht AND
=AND(EXACT(LEFT(I5,1),"A"),LEN(RIGHT(I5,LEN(RIGHT(I5)-1))=9,ISNUMBER(RIGHT(I5,LEN(RIGHT(I5)-1)*1))
Then combine this with "A R" condiytion with OR
=OR(EXACT(LEFT(I5,3),"A R");AND(EXACT(LEFT(I5,1),"A"),LEN(RIGHT(I5,LEN(RIGHT(I5)-1))=9,ISNUMBER(RIGHT(I5,LEN(RIGHT(I5)-1)*1)))
- Errol- thanks for the prompt reply. Unfortunately Excel would not accept it saying there is an error in the formula I copied from your reply. I am running MS Office Professional Plus 2013. Can you help? Thanks!
Hi,
Replace any ";" with "," please. I am using different language settings and in this settings we are using ";". Best regards,
