SOLVED

# Data Validation with AND and OR

Highlighted
Occasional Contributor

# 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!

8 Replies
Highlighted

# Re: Data Validation with AND and OR

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)))

Highlighted

# Re: Data Validation with AND and OR

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!
Highlighted

# Re: Data Validation with AND and OR

Hi,

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

Highlighted

# Re: Data Validation with AND and OR

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!

Highlighted
Best Response confirmed by Eric_Marcus (Occasional Contributor)
Solution

# Re: Data Validation with AND and OR

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.

Highlighted

# Re: Data Validation with AND and OR

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

Highlighted

# Re: Data Validation with AND and OR

trust me, I am an engineer..

Highlighted

# Re: Data Validation with AND and OR

And obviously a VERY good one ... thank you!