0

Avoid using multiple REPLACE and/or CONTAINS multiple times

SUBSTITUTE_REGEX can be used in a number of creative ways, to do things we can't normally do.

But don't worry! You don't need to know any regex to know how to do these things.

For the formulas, you can copy and paste them directly, and just replace the parts that are underlined..
Options:
-REPLACE multiple things(if they are an exact match)
-REPLACE multiple things, if they contain something specific
-REPLACE multiple things, if they contain any of multiple keywords

 

  • REPLACE multiple things(if they are an exact match)

    The structure you need is as follows:
    SUBSTITUTE_REGEX(
           words,
           CONCAT(
                "^",
               words to replace,
               "$"
           ),

           words to replace them with
    )

    For example:
    SUBSTITUTE_REGEX(
            ARRAY("aa,bb,cc"),
            CONCAT(
                  "^",
                  ARRAY("aa,b,cc"),
                 "$"
            ),

            ARRAY(1,2,3)
    )



  • REPLACE multiple things, if they contain something specific

    The structure you need is as follows:
    SUBSTITUTE_REGEX(
           words,
           CONCAT(
                  "[\s\S]*(",
                  words to replace,
                  ")[\s\S]*"
           ),

           words to replace them with
    )

    For example:
    SUBSTITUTE_REGEX(
        ARRAY("aa,bb,cc"),
        CONCAT(
                "[\s\S]*(",
                ARRAY("aa,b,cc"),
                ")[\s\S]*"
         ),

        ARRAY(1,2,3)
    )



  • REPLACE multiple things, if they contain any of multiple keywords
    The structure you need is as follows:
    SUBSTITUTE_REGEX(
           words,
           CONCAT(
                  "[\s\S]*(",
                  word1|word2|word3,
                  ")[\s\S]*"
            ),

           words to replace them with
    )

    For example:
    SUBSTITUTE_REGEX(
        ARRAY("marketing world,xx,cars auto shop"),
        CONCAT(
              "[\s\S]*(",
              ARRAY("marketing|social media,000,shop|place|store"),
              ")[\s\S]*"
        ),

        ARRAY(1,2,3)
    )

    The ideal practice is to set up a datasource with the word to replace in one column, and the keyword to search for in another column.
    Eg, this example here is a datasource with the replacing word in column A, and in column b the keyword to look for.


    You can then do this structure
    SUBSTITUTE_REGEX(
          words,
          CONCAT(
                "[\s\S]*(",
                 GROUPBY(
                       A:A,//replacing word in keyword file
                       B:B,//keyword in keyword file
                       "join(values,\"|\")"
                 ),
                 ")[\s\S]*"),
                 GROUP(A:A)
           )
    )



  • REPLACE part of a string, replacing multiple words

    Use SUBSTITUTE instead, since it can pass in multiple values for 2nd and third parameter

0 comments

Please sign in to leave a comment.