4 Replies Latest reply on Oct 26, 2016 8:21 AM by Samuel Gee

    Wildcard in CONTAINS ?

    Samuel Gee



      Many of my products are listed as:


      Label 1

      Label 2

      Label 3


      But many more are:




      I would like a CONTAINS formula that will pick up on anything in the format "XXX/XXX/XXX". So text slash text slash text. The actual labels are too varied to list out by hand.


      Does this exist? I can't find any wildcard characters in the help documentation.





        • 1. Re: Wildcard in CONTAINS ?
          Joe Oppelt

          Your question is vague.

          First, there is no wildcard in contains.  So don't bang your head on that wall. 


          CONTAINS will find the string "Label" whether it's stand-alone, or embedded in a long string with slashes.  But I'm guessing you're not looking for that.


          CONTAINS will tell you a label contains slashes if that's what you look for, so it will tell you it has the format "XXX/XXX/XXX".  But I'm guessing you're not looking for that either.


          So what are you actually looking for?


          Maybe the REGEX functions will do what you need:



          • 2. Re: Wildcard in CONTAINS ?
            Samuel Gee

            My apologies for the lack of clarity.


            In a list of hundreds of thousands of product names, I want to isolate those that are in the format XXX/XXX/XXX.


            I can't just use CONTAINS([Product Name],"/"), as there are products with a / in but not in the XXX/XXX/XXX format, which is specifically what I need to isolate.


            I'll check the REGEX, thank you.

            • 3. Re: Wildcard in CONTAINS ?
              Joe Oppelt

              Does that mean you need slashes in positions 4 and 8, and be exactly 11 long?  Or that you have to have exactly 2 slashes, with varying lengths of characters in between?  Can there be spaces?  What if your label looked like this:




              Would you want the calc to count that one as a hit?


              The reason I am asking all these questions is that there are other things you can do to find xxx/xxx/xxx.  You could REPLACE "/" with "" (null) and if the length of the string is two less than before, then you had two slashes.  Or you could specifically see if MID([Label],4,1) = "/" (and same for position 8).  but that only works if you have specifically 3-char chunks of XXX.


              There is a FINDNTH function that will let you look for the first or second (or n-th) occurrence of a string in a field.  You can use that to see of the slashes are where you need them to be.  (And if there actually are 2 slashes.  And even if there are THREE slashes, which would probably tell you the string is not in the format xxx/xxx/xxx.)


              Just some examples of ideas you can explore if REGEX can't do what you need.

              • 4. Re: Wildcard in CONTAINS ?
                Samuel Gee

                Thanks Joe.


                I have to have three slashes, but the labels between them can be of varying lengths, and yes the date you listed would count as a hit.


                I'm investigating REGEX right now as it's probably time I learned some, but those other options will be really useful when I invariably give up and look for the quick fix


                Much obliged!