Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Regex replace (or formula) find and delete multi strings

Topic Labels: Formulas
Solved
Jump to Solution
282 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

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 !