Skip to main content
Solved

Regex replace (or formula) find and delete multi strings

  • July 29, 2022
  • 2 replies
  • 73 views
P

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

Best answer by kuovonne

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

2 replies

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • July 30, 2022

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}
)
TheTimeSavingCo
P
P
  • Patakess
  • Author
  • New Participant
  • July 30, 2022

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

Hello,

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

Thanks again !

kuovonne
Terms & ConditionsAccessibility statement