SOLVED

Data Validation with AND and OR

%3CLINGO-SUB%20id%3D%22lingo-sub-1361776%22%20slang%3D%22en-US%22%3EData%20Validation%20with%20AND%20and%20OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1361776%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20use%20data%20validation%20on%20a%20cell%20to%20ensure%20that%20it%20is%20either%20an%20%22A%22%20followed%20by%208%20numbers%20OR%20starts%20with%20%22A%20R%22.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20do%20just%20the%20latter%20with%20%3DEXACT(LEFT(I5%2C3)%2C%22A%20R%22).%3C%2FP%3E%3CP%3EI%20can%20do%20an%20%22A%22%20with%20length%20of%209%20with%26nbsp%3B%3DOR(EXACT(LEFT(I5%2C3)%2C%22A%22)%2CLEN(I4)%3D9)%20but%20I%20cannot%20do%20a%20numeric%20check%20on%20the%20eight%20digits%20following%20the%20%22A%22%20AND%20I%20cannot%20combine%20both%20in%20an%20OR.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20appreciate%20any%20assistance%20-%20thanks%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1361776%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1362317%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20with%20AND%20and%20OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1362317%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F656688%22%20target%3D%22_blank%22%3E%40Eric_Marcus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20codition%20starts%20with%20A%2C%20followed%20by%209%20digit%20number%3A%3C%2FP%3E%3CP%3E%3DEXACT(LEFT(I5%3B1)%2C%22A%22)%3C%2FP%3E%3CP%3E%3DLEN(RIGHT(I5%2CLEN(RIGHT(I5)-1))%3D9%3C%2FP%3E%3CP%3E%3DISNUMBER(RIGHT(I5%2CLEN(RIGHT(I5)-1)*1)%3C%2FP%3E%3CP%3ECombine%20them%20all%20wiht%20AND%3C%2FP%3E%3CP%3E%3DAND(EXACT(LEFT(I5%2C1)%2C%22A%22)%2CLEN(RIGHT(I5%2CLEN(RIGHT(I5)-1))%3D9%2CISNUMBER(RIGHT(I5%2CLEN(RIGHT(I5)-1)*1))%3C%2FP%3E%3CP%3EThen%20combine%20this%20with%20%22A%20R%22%20condiytion%20with%20OR%3C%2FP%3E%3CP%3E%3DOR(%3CSPAN%3EEXACT(LEFT(I5%2C3)%2C%22A%20R%22)%3BAND(EXACT(LEFT(I5%2C1)%2C%22A%22)%2CLEN(RIGHT(I5%2CLEN(RIGHT(I5)-1))%3D9%2CISNUMBER(RIGHT(I5%2CLEN(RIGHT(I5)-1)*1)))%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1364207%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20with%20AND%20and%20OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1364207%22%20slang%3D%22en-US%22%3EErrol-%20thanks%20for%20the%20prompt%20reply.%20Unfortunately%20Excel%20would%20not%20accept%20it%20saying%20there%20is%20an%20error%20in%20the%20formula%20I%20copied%20from%20your%20reply.%20I%20am%20running%20MS%20Office%20Professional%20Plus%202013.%20Can%20you%20help%3F%20Thanks!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1364289%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20with%20AND%20and%20OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1364289%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F656688%22%20target%3D%22_blank%22%3E%40Eric_Marcus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EReplace%20any%20%22%3B%22%20with%20%22%2C%22%20please.%20I%20am%20using%20different%20language%20settings%20and%20in%20this%20settings%20we%20are%20using%20%22%3B%22.%20Best%20regards%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1364376%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20with%20AND%20and%20OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1364376%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F232473%22%20target%3D%22_blank%22%3E%40erol%20sinan%20zorlu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20erol%20(and%20sorry%20for%20misspelling%20your%20name%20in%20my%20last%20post%20--%20MS%20autocorrected).%26nbsp%3B%20I%20replaced%20the%20one%20%3B%20with%20%2C%20but%20it%20still%20was%20not%20accepted.%26nbsp%3B%20%26nbsp%3BAny%20other%20thoughts%3F%26nbsp%3B%20Thanks%20again!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1364419%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20with%20AND%20and%20OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1364419%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F656688%22%20target%3D%22_blank%22%3E%40Eric_Marcus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20Below%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DOR(EXACT(LEFT(I5%2C3)%2C%22A%20R%22)%2CAND(EXACT(LEFT(I5%2C1)%2C%22A%22)%2CLEN(RIGHT(I5%2CLEN(I5)-1))%3D9%2CISNUMBER(RIGHT(I5%2CLEN(I5)-1)*1)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20a%20different%20language%20version%20of%20Excel%20if%20this%20doesn't%20work%20please%20rewrite%20the%20formulas%20I%20gave%20on%20my%20first%20post%20seperately%20to%20be%20sure%20they%20run%20correctly%20and%20then%20combine%20them.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1364550%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20with%20AND%20and%20OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1364550%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F232473%22%20target%3D%22_blank%22%3E%40erol%20sinan%20zorlu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EErol%20...%20that%20was%20perfect%20...%20thank%20you%20so%20much!%26nbsp%3B%20I%20was%20having%20trouble%20combining%20ANDs%20and%20ORs%20...%20you%20did%20great!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1364589%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20with%20AND%20and%20OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1364589%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F656688%22%20target%3D%22_blank%22%3E%40Eric_Marcus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Etrust%20me%2C%20I%20am%20an%20engineer..%20%3CLI-EMOJI%20id%3D%22lia_grinning-face-with-smiling-eyes%22%20title%3D%22%3Agrinning_face_with_smiling_eyes%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1364984%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20with%20AND%20and%20OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1364984%22%20slang%3D%22en-US%22%3EAnd%20obviously%20a%20VERY%20good%20one%20...%20thank%20you!%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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
Highlighted

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

Highlighted
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

@Eric_Marcus 

Hi,

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

Highlighted

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

Highlighted
Best Response confirmed by Eric_Marcus (Occasional 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.

Highlighted

@erol sinan zorlu 

 

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

Highlighted

@Eric_Marcus 

trust me, I am an engineer..

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