Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Regex expression for matching previous capture group

Topic Labels: Formulas
57 2
cancel
Showing results for 
Search instead for 
Did you mean: 

I'm trying to clean up my text with Regex. Some ranges are listed as 3.0 - 3.0 which I want simplified to just 3.0 whenever the two numbers match. I tried using regex101 and MDN references, but Airtable seems to use some different syntax than either of those.

The following expressions should be matching but aren't. Does Airtable not support these "back reference" expressions such as \g{n} or simply \n

  • (\d.*) - (\g{1})
  • (\d.*) - (\1)

Desired Result:

3.0 - 4.0 ==should remain 3.0 - 4.0
3.0 - 3.0 ==should change to 3.0
1.5 - 1.5 ==should change to 1.5
1.0 - 3.5 ==should remain 1.0 - 3.5

 

2 Replies 2

Airtable regular expressions use Google's RE2 library. I recommend building this as three formulas to start. One formula to extract the first value, another to extract the second value, and a third formula to product the result. Once you have this working as three formula fields, you can then condense them back down to one.

Hey @mattstevenson

Here's a formula that achieves something along the lines of what you're looking for:

IF(
    {String},
    IF(
        AND(
            REGEX_MATCH(
                {String},
                "^\\d\\.\\d\\s"
            ),
            REGEX_MATCH(
                {String},
                "-\\s\\d\\.\\d$"
            )
        ),
        IF(
            REGEX_EXTRACT({String}, "^\\d\\.\\d") = REGEX_EXTRACT({String}, "\\d\\.\\d$"),
            REGEX_EXTRACT({String}, "^\\d\\.\\d"),
            {String}
        ),
        {String}
    )
)

Here's a look at what it looks like in Airtable:

Ben_Young1_1-1674664128946.png