conditional formatting, REGEX to excel, do not want VBA, for tag numbers

[cty43945]

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

 

[Marcelo Branco]

I just ran into that. I also could use the OR(ISNUMBER(VALUE(LEFT($C2,2))),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") part

Yes, this works but if the list gets bigger the construct Match/Named_Range is more flexible and you can use a shorter formula.

M.

 

[cty43945]

I should mention that the age can also be 2Y2M, 2Y12M, etc

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

 

[Marcelo Branco]

I did not look at question 3. And I have to leave now.
Maybe someone else can help you, but I think you should better describe question 3 (I did not quite understand ...).
Some examples might help.

M.

 

[cty43945]

1) and 2) are pretty much complete. 3) still needs work. It is an age or birthdate column. It needs to be able to accept M/D/YYYY or MM/DD/YYYY,
AND it needs to accept duration of age:
1-999 followed by D or M or Y, ###D , ###M , ###Y AND 1-99 followed by Y then followed by 1-12 and an M.
For instance, 7D would be a 7D old calf. 1Y could be a year old steer. 1Y2M would be a 1 year and 2 month old calf.
^(([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}))$