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

    Wildcard in CONTAINS ?

    Samuel Gee

      Hi!

       

      Many of my products are listed as:

       

      Label 1

      Label 2

      Label 3

       

      But many more are:

       

      Label/Label2/Label3

       

      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.

       

      Thanks!

       

      Sam

        • 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:

           

          http://visualbi.com/blogs/data-discovery/introduction-regex-calculations-tableau-9-0/

          • 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:

               

              October/26/2016

               

              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!