May 05 2020 03:52 PM
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!
May 05 2020 11:17 PM
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)))
May 06 2020 08:29 AM
May 06 2020 08:44 AM
Hi,
Replace any ";" with "," please. I am using different language settings and in this settings we are using ";". Best regards,
May 06 2020 09:02 AM
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!
May 06 2020 09:16 AM
SolutionTry 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.
May 06 2020 10:09 AM
Erol ... that was perfect ... thank you so much! I was having trouble combining ANDs and ORs ... you did great!
May 06 2020 10:20 AM - edited May 06 2020 10:21 AM
trust me, I am an engineer.. :D
May 06 2020 12:11 PM
May 06 2020 09:16 AM
SolutionTry 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.