3 Replies Latest reply on Sep 30, 2016 5:29 AM by ra.sae.0

    Filtering on a concatenated string

    Matthew Perry

      Hi there,


      I have 1 row per person, with a column for a concatenated list of colours....eg.


      FredRed, Blue, Green
      GeorgeRed, Blue


      I would like to have a filter, where I can choose "Red" to show all three people, "Blue" to show Fred and George and "Green" to show only Fred.


      But how to create a filter that pulls apart the concatenated values.


      TIA Matt

        • 1. Re: Filtering on a concatenated string

          Hi Matthew,

          There are multiple ways to achieve it. Few simple ways are attached image & Workbook. Please see to it.


          Hope this helps.



          Option 1 - Use Wildcard Match Filter

          Option 2 - Use Parameter & Calculated Field to Filter


          • 2. Re: Filtering on a concatenated string
            Matthew Perry

            Thank you for your response. Yes, these are methods I have tried and work for a single colour selection. What I mistakenly left out of the original question is that it is necessary to be able to filter by 2 (or more) colours (or any number >=1).....

            • 3. Re: Filtering on a concatenated string

              Another Option 1 :


              Having multiple Parameters to filter them


              Another Option 2:

              Using Split function to Filter using one Parameter



              contains([Concat Colours],trim(split([Filter Parameter],";",1)) )



              ELSEIF len(split([Filter Parameter],";",2)) > 0

              AND contains([Concat Colours],trim(split([Filter Parameter],";",2)) )  then


              ELSEIF len(split([Filter Parameter],";",3)) > 0

              AND contains([Concat Colours],trim(split([Filter Parameter],";",3)) )  then


              ELSEIF len(split([Filter Parameter],";",4)) > 0

              AND contains([Concat Colours],trim(split([Filter Parameter],";",4)) )  then


              ELSEIF len(split([Filter Parameter],";",5)) > 0

              AND contains([Concat Colours],trim(split([Filter Parameter],";",5)) )  then









              ; helps to make multiple list and above formula can help up to 5 values, if u need more, just copy the last lines and add 6 , 7 to it.