Help

Regex replace (or formula) find and delete multi strings

Topic Labels: Formulas
Solved
Jump to Solution
1022 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Patakess
4 - Data Explorer
4 - Data Explorer

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

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

Welcome to the Airtable community!

Is this what you want?
image

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}
)

See Solution in Thread

2 Replies 2
kuovonne
18 - Pluto
18 - Pluto

Welcome to the Airtable community!

Is this what you want?
image

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}
)

Hello,

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

Thanks again !