Page 1 of 2 12 LastLast
Results 1 to 10 of 14

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

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    New Member
    Join Date
    May 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    2) This highlighted the lower case, but it doesn't seem to maintain the len = 9...

    =ISERR(NOT(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)))))

  3. #3
    New Member
    Join Date
    May 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    2) I was able to get rid of needing a named cell by completely changing my strategy of how to do this.

    =EXACT($C2,LOWER($C2)) compares the actual value, to lowercase. If it is lowercase, it will be true. Could use upper to flip result (SEARCH is also case sensitive, but can't really use that one)

    =AND(ISERR(VALUE(RIGHT($C2,5))>10000))
    This formula looks at the last 5 digits, if there is 5, it will be greater than 10000. So it looks, If it is true, it will be red. If it is not, it will throw an error, which is why the ISERR( function is there. This will flip what you think would be returned. So if there are 5 digits on the end, then it will not throw an error, so false. If there isn't, true.

    This works perfectly except it is still flagging blanks. The work around for blanks was an IF statement.

    =IF(ISBLANK($C2),"FALSE",NOT(AND(ISNUMBER(VALUE(RIGHT($C2,4))),NOT(ISNUMBER(VALUE(MID($C2,3,3)))),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"),LEN(C2)=9,ISERR(VALUE(RIGHT($C2,5))>10000),EXACT($C2,UPPER($C2)))))

    This one is complete. Any advice on 1) or 3) would be greatly appreciated

  4. #4
    New Member
    Join Date
    May 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    1) is finished. =IF(ISBLANK($D2),"FALSE",NOT(AND(ISNUMBER(VALUE($D2)),LEN($D2)=15,OR(AND(LEFT($D2,3)<>"999",LEFT($D2,3)>="900"),LEFT($D2,6)="840003",LEFT($D2,3)="124",LEFT($D2,3)="484"))))

    Any advice will be appreciated

  5. #5
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,104
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

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

    Thoughts about 2)
    To ckeck the length...
    LEN(A2)=9

    To check the first two characters...
    OR(ISNUMBER(--LEFT(A2,2)),ISNUMBER(MATCH(LEFT(A2,2),{"MD";"MN";"NM";"NY";"US";"WA";"WY"},0)))

    To check if the 3rd, 4th and 5th characters are letters...
    EXACT(UPPER(MID(A2,3,1)),LOWER(MID(A2,3,1)))=FALSE
    EXACT(UPPER(MID(A2,4,1)),LOWER(MID(A2,4,1)))=FALSE
    EXACT(UPPER(MID(A2,5,1)),LOWER(MID(A2,5,1)))=FALSE

    To check if the 4 last characters are numbers...
    ISNUMBER(--MID(A2,6,4))

    M.
    Last edited by Marcelo Branco; May 24th, 2018 at 06:19 PM.

  6. #6
    New Member
    Join Date
    May 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    I will definitely incorporate these in. The one issue I ran into is a common error is that they drop a letter, and add a number at the end, but the more thorough check on the 3rd, 4th, and 5th characters as letters should cover that.

    Thank you for your help! I am trying it out now
    Last edited by cty43945; May 24th, 2018 at 06:28 PM.

  7. #7
    New Member
    Join Date
    May 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    also, I see it quite often, what is the "--" for before MID( and LEFT( ?

  8. #8
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,104
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

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

    Quote Originally Posted by cty43945 View Post
    also, I see it quite often, what is the "--" for before MID( and LEFT( ?
    -- converts, for example, a text like "11" provided by LEFT(A2,2) in the number 11

    M.

  9. #9
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,104
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

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

    oops...

    I think you cannot use array constants like {"MD";"MN";"NM";"NY";"US";"WA";"WY"} in Conditional Formatting.
    So to check the first two characters create a named range, say MyList, and use
    OR(ISNUMBER(--LEFT(A2,2)),ISNUMBER(MATCH(LEFT(A2,2),MyList,0)))

    M.

  10. #10
    New Member
    Join Date
    May 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •