SOLVED

Data Validation with AND and OR

Copper Contributor

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!

8 Replies

@Eric_Marcus 

 

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!

@Eric_Marcus 

Hi,

Replace any ";" with "," please. I am using different language settings and in this settings we are using ";". Best regards,

@erol sinan zorlu 

 

Thank you erol (and sorry for misspelling your name in my last post -- MS autocorrected).  I replaced the one ; with , but it still was not accepted.   Any other thoughts?  Thanks again!

best response confirmed by Eric_Marcus (Copper Contributor)
Solution

@Eric_Marcus 

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.

@erol sinan zorlu 

 

Erol ... that was perfect ... thank you so much!  I was having trouble combining ANDs and ORs ... you did great!

@Eric_Marcus 

trust me, I am an engineer.. :D

And obviously a VERY good one ... thank you!
1 best response

Accepted Solutions
best response confirmed by Eric_Marcus (Copper Contributor)
Solution

@Eric_Marcus 

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.

View solution in original post