Regex replace (or formula) find and delete multi strings

[Patakess]

Hello !

I’m looking for a solution to find the different strings in one fret and erase them in another.
For the moment it looks like this:

REGEX_REPLACE(Concat, Del,""))

But it doesn’t work if in “Del” I put:

8hKitchen 9hRoom

And in Concat

9hRoom 10hDiner 8hKitchen 11hBathroom

I would like to delete: “8hKitchen” and “9hRoom” (but it can also be of other occurrence, more or less.)

Someone would have any idea ?

Thanks in advance

[kuovonne · Accepted solution]

Welcome to the Airtable community!

Is this what you want?

RegEx can be tricky to craft. Your expression wasn’t working because the formula was looking for the exact string in {Del}. However, you want to look for any word in {Del} which requires a different approach. In this formula I use a RegEx to convert the contents of {Del} to a RegEx expression that looks for any of the individual words in {Del}, then applied that RegEx expression to {Concat}. I also threw in a little additional handing of spaces for good measure.

IF(
  {Del},
  TRIM(
    REGEX_REPLACE(
      {Concat}, 
      CONCATENATE(
        "(",
        REGEX_REPLACE(
          TRIM({Del}), 
          " +", 
          " *)|("
        ),
        " *)"
      ),
      ""
    )
  ),
  {Concat}
)

[Patakess]

Hello,

Thanks for your welcome !
And thanks for the answer. I would never have found! It’s work perfectly !

Thanks again !