6 Replies Latest reply on Feb 5, 2019 4:24 PM by Nick Parsons

    "REPLACE" with wildcards?

    Craig Phillips

      I am having trouble figuring out how to do a "REPLACE" in a calculated field with a wildcard.

       

      Specifically, I'm trying to remove everything within a set of parentheses for a returned field value.

       

      For example, if I have a value of "Address (home)" or "Address (work)" or "Address (vacation") I want to have the calculated field return back just "Address".  I realize I can do nested "REPLACE" commands, but I want my value to automatically account for any new values within the parentheses.

        • 1. Re: "REPLACE" with wildcards?
          Nick Parsons

          Create a calculation field as...

           

          REGEXP_REPLACE([Value],"\([^()]*\)", "")

           

          Screen Shot 2019-02-05 at 2.39.11 PM.png

          • 2. Re: "REPLACE" with wildcards?
            Gerardo Varela

            Hi Craig,

            If your data source doesn't support regular expressions:

             

            Become a regular with regular expressions

             

            You may want to try using trim and split:

             

            Split a Field into Multiple Fields - Tableau

             

            Nested trim split statement:

             

            TRIM( SPLIT( TRIM( SPLIT( [Blah], "(", 2 ) ), ")", 1 ) )

             

             

             

            [Blah] is the dimension you are trying to clean up.

             

            Regards,

            Gerardo

            • 3. Re: "REPLACE" with wildcards?
              Joe Oppelt

              Nick -- Some day I need to learn REGEXP functions.  That's a great solution, Nick.


              If a string has two parenthetical clauses in it, would that chop out both of them?

               

              Craig -- if your string is always in the format of "The piece of string you want to keep" followed by the parenthetical clause you want to chop out, you could use string functions.

               

              FIND([string field], <substring value>) will give you the position of the request substring.


              Then you could do a MID() function (which pulls out the specified piece of the string) or a LEFT function (which keeps x-many characters at the left of the input string.


              So

               

              LEFT( [address string], (FIND([address string], "(" )-1 )

               

              would pull out from character 1 up to (but not including) the opening parenthesis.  (And you would probably want to check if that LEFT function returns zero, which means the paren was not found, and then you would just want to grab the whole value of [address string].)

               

              If you need to grab stuff after the closing parenthesis, you could do the same thing with a RIGHT function and add them together.

               

              LEFT( ....) + RIGHT( ... )

               

              and maybe add a space between them.

               

              But wow.  REGEXP stuff.  Some day I need to learn that.

              • 4. Re: "REPLACE" with wildcards?
                Joe Oppelt

                Cool replies so far.


                Lots of ways to address this!

                • 5. Re: "REPLACE" with wildcards?
                  Gerardo Varela

                  Yup, multiple solutions! Except that Joe and myself misread the question. He just wants to return address   That would be easier as long as the data follows the same pattern:

                   

                  TRIM( SPLIT( [Blah], " ", 1 ) )

                   

                  That would take care of it.  Splitting off everything before the first space. If you have a larger sample data set we can see if the above solutions need to be adjusted or not.

                   

                  I'm with Joe, I always tell myself I'll learn REGEX!  I never do because my stubborn side tells this can be done in Tableau and I'll eventually figure it out.  

                   

                  Regards,

                  Gerardo

                  • 6. Re: "REPLACE" with wildcards?
                    Nick Parsons

                    Thanks Joe, regex can be like pulling off a good card trick with a lot of failed "is this your card?" attempts.

                     

                    The one I posted would remove all instances of parentheses groups. It didn't remove inner parens but with a small tweak, a "*" after the group and it does. Not the Craig asked for that, but just some more fun with regex.

                     

                    Screen Shot 2019-02-05 at 4.17.56 PM.png