Forum Discussion

Eric_Marcus's avatar
Eric_Marcus
Copper Contributor
May 05, 2020
Solved

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 c...
excel
Formulas and Functions
  • erol sinan zorlu's avatar
    erol sinan zorlu
    May 06, 2020

    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's avatar
erol sinan zorlu
Iron Contributor
May 06, 2020

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

Eric_Marcus's avatar
Eric_Marcus
Copper Contributor
May 06, 2020
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!
  • erol sinan zorlu's avatar
    erol sinan zorlu
    Iron Contributor
    May 06, 2020

    Eric_Marcus 

    Hi,

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

    • Eric_Marcus's avatar
      Eric_Marcus
      Copper Contributor
      May 06, 2020

      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!

      • erol sinan zorlu's avatar
        erol sinan zorlu
        Iron Contributor
        May 06, 2020

        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.

Resources