I am trying to make a workbook that ranchers and farmers can use (for free) for livestock inventory that does not use VBA (know some excel, and seem to always be learning more), to improve animal disease traceability.
1) AIN tag (15 characters), is ^(?!999)((840)|(124)|(484)|(9[0-9]{2}))[0-9]{12}$ , but even this could be updated. The 840 at the beginning, is always 840003, and then 9 more random digits. I thought it would be easy, but every time I think I have it, it doesn't work. I want the cell to highlight when entered incorrectly, but not highlight if nothing is entered... I am new to this, but thus far have : =NOT(AND(LEN($D2)=15,ISNUMBER($D2),OR(LEFT($D2,3)<>"999",LEFT($D2,6)="840003",LEFT($D2,3)="124",LEFT($D2,3)="484",AND(LEFT($D2,3)>"899",LEFT($D2,3)<"999",NOT(ISBLANK($D2)))))), which is not correct yet.
2) NUES tag (9 characters) is ^([0-9]{2}|MD|MN|NM|NY|US|WA|WY)[A-Z]{3}[0-9]{4}$ The first two characters are the numeric state code, or sometimes also the actual state abbreviation (for those listed). Then it is 3 letters, and 4 numbers. I found an example, modified its logic, and it almost works. But if possible, I do not want a reference cell. The letters do need to be all caps (for this and following example). I have =AND(LEN($C2)=9,OR(ISNUMBER(-MID($C2,ROW(INDIRECT("1:2")),1)),LEFT($C2,2)="MD",LEFT($C2,2)="MN",LEFT($C2,2)="NM",LEFT($C2,2)="NY",LEFT($C2,2)="US",LEFT($C2,2)="WA",LEFT($C2,2)="WY"),FIND(MID($C2,ROW(INDIRECT("3:5")),1),Letters),ISNUMBER(-MID($C2,ROW(INDIRECT("6:9")),1))), and Letters is "ABCDEFGHIJKLMNOPQRSTUVWXYZ". It works, but when lowercase letters are entered, it does not highlight, #VALUE . I would like to not use a reference if possible (the A-Z). I know how to make string upper, or lower, but not make a true false out of it. Also what I have highlights all empty cells. I was trying the Or Not(IsBlank... but it kept breaking it...
3) and AGE/Birthdate column: This can be 3M for 3 Months, 4Y for 4 years, or can be the actual birthdate.
^(([1-9][0-9]{0,2})(D|M|Y))$|^([1-9][0-9]{0,1}Y(([1][0-2])|[1-9])M)$|^((0?[13578]|10|12)(\/)(([1-9])|(0[1-9])|([12])([0-9]?)|(3[01]?))(\/)((19)([8-9])(\d{1})|(20)([0-2])(\d{1})))|(0?[2469]|11)(\/)(([1-9])|(0[1-9])|([12])([0-9]?)|(30))(\/)((19)([8-9])(\d{1})|(20)([0-2])(\d{1}))$
There are more tag types, but if I can get help with these three, I can modify them to create the others. Thank you
1) AIN tag (15 characters), is ^(?!999)((840)|(124)|(484)|(9[0-9]{2}))[0-9]{12}$ , but even this could be updated. The 840 at the beginning, is always 840003, and then 9 more random digits. I thought it would be easy, but every time I think I have it, it doesn't work. I want the cell to highlight when entered incorrectly, but not highlight if nothing is entered... I am new to this, but thus far have : =NOT(AND(LEN($D2)=15,ISNUMBER($D2),OR(LEFT($D2,3)<>"999",LEFT($D2,6)="840003",LEFT($D2,3)="124",LEFT($D2,3)="484",AND(LEFT($D2,3)>"899",LEFT($D2,3)<"999",NOT(ISBLANK($D2)))))), which is not correct yet.
2) NUES tag (9 characters) is ^([0-9]{2}|MD|MN|NM|NY|US|WA|WY)[A-Z]{3}[0-9]{4}$ The first two characters are the numeric state code, or sometimes also the actual state abbreviation (for those listed). Then it is 3 letters, and 4 numbers. I found an example, modified its logic, and it almost works. But if possible, I do not want a reference cell. The letters do need to be all caps (for this and following example). I have =AND(LEN($C2)=9,OR(ISNUMBER(-MID($C2,ROW(INDIRECT("1:2")),1)),LEFT($C2,2)="MD",LEFT($C2,2)="MN",LEFT($C2,2)="NM",LEFT($C2,2)="NY",LEFT($C2,2)="US",LEFT($C2,2)="WA",LEFT($C2,2)="WY"),FIND(MID($C2,ROW(INDIRECT("3:5")),1),Letters),ISNUMBER(-MID($C2,ROW(INDIRECT("6:9")),1))), and Letters is "ABCDEFGHIJKLMNOPQRSTUVWXYZ". It works, but when lowercase letters are entered, it does not highlight, #VALUE . I would like to not use a reference if possible (the A-Z). I know how to make string upper, or lower, but not make a true false out of it. Also what I have highlights all empty cells. I was trying the Or Not(IsBlank... but it kept breaking it...
3) and AGE/Birthdate column: This can be 3M for 3 Months, 4Y for 4 years, or can be the actual birthdate.
^(([1-9][0-9]{0,2})(D|M|Y))$|^([1-9][0-9]{0,1}Y(([1][0-2])|[1-9])M)$|^((0?[13578]|10|12)(\/)(([1-9])|(0[1-9])|([12])([0-9]?)|(3[01]?))(\/)((19)([8-9])(\d{1})|(20)([0-2])(\d{1})))|(0?[2469]|11)(\/)(([1-9])|(0[1-9])|([12])([0-9]?)|(30))(\/)((19)([8-9])(\d{1})|(20)([0-2])(\d{1}))$
There are more tag types, but if I can get help with these three, I can modify them to create the others. Thank you
