New: REGEX formula functions

[Jason]

Hello everyone! We recently launched some exciting new additions to our formula field with three new regex (regular expression) functions:

1. REGEX_MATCH(string, regex)
2. REGEX_EXTRACT(string, regex)
3. REGEX_REPLACE(string, regex, replacement)

These functions can be used to match character combinations within text strings. For those interested, Airtable’s REGEX functions are implemented using the RE2 regular expression library.

You can learn more about these functions in this article or check out some example use cases shared in the posts below.

[Miles_Mattison]

I’d just REGEX REPLACE for this to match everything but the first letter and replace it with “”. As follows:

REGEX_REPLACE({Field},"[a-z]+[a-z\s]","")

This matches whole words, minus the first character
REGEX_REPLACE({Field},’(\B\w)*’,’’)

But yes, none of the REGEX commands use the global flag and will stop at the first match

[jj23]

I’m getting an error using REGEX_REPLACE even though the formula works on regex101. I’m trying to add or to the last item in a series.

My formula in Airtable:
REGEX_REPLACE(String,"/,(?=[^,]*$)",", or")

Any ideas?

[Justin_Barrett]

Welcome to the community, @jj23! :grinning_face_with_big_eyes: When testing on Regex101, you need to use the Golang variant (originally labeled “RE2” when these functions were first released), which apparently doesn’t support the “?=” syntax. The “PCRE2” variant where this does work is apparently not the same variant of RE2 as the Golang version.

As a side note, you don’t need to copy the opening forward slash (or, for Golang, the graves). Those surrounding symbols are only required when pasting the regex directly into the relevant scripting language. For Airtable, you’re passing the regex as a string, so it doesn’t need the extra formatting characters.

After a little fiddling, I got this to work:

REGEX_REPLACE(String, ",([^,]*$)", ", or$1")

[Russell_Bishop1]

Absolutely glorious @Justin_Barrett , thank you. Now my “Artists” field displays beautifully:

“Sunchase, NickBee & Teddy Killerz”

[Justin_Barrett]

@Russell_Bishop1 Thanks, but props go to @jj23 for working out most of the regex. My cleanup work was minimal by comparison.

[Benjamin_Carrio]

Hi all !

I’am try to use REGEX_REPLACE to replace , by \n and it’s works in this case:

REGEX_REPLACE("test,test", ",", "\n")

In output, I have:

test
test

Now, I have a lookup field with this content:

<Location /gazelle> ProxyPass ajp://127.0.0.1:8531/gazelle ProxyPassReverse ajp://127.0.0.1:8531/gazelle </Location>, <Location /gazelle-tm-ejb> ProxyPass ajp://127.0.0.1:8531/gazelle-tm-ejb ProxyPassReverse ajp://127.0.0.1:8531/gazelle-tm-ejb </Location>, <Location /gss> ProxyPass ajp://127.0.0.1:8527/gss ProxyPassReverse ajp://127.0.0.1:8527/gss </Location>, <Location /gazelle-atna-ejb> ProxyPass ajp://127.0.0.1:8527/gazelle-atna-ejb ProxyPassReverse ajp://127.0.0.1:8527/gazelle-atna-ejb </Location>, <Location /gazelle-xua-jar> ProxyPass ajp://127.0.0.1:8527/gazelle-xua-jar ProxyPassReverse ajp://127.0.0.1:8527/gazelle-xua-jar </Location>, <Location /xml-signature-validator-jar> ProxyPass ajp://127.0.0.1:8527/xml-signature-validator-jar ProxyPassReverse ajp://127.0.0.1:8527/xml-signature-validator-jar </Location>, <Location /gazelle-webservice-tester> ProxyPass ajp://127.0.0.1:8533/gazelle-webservice-tester ProxyPassReverse ajp://127.0.0.1:8533/gazelle-webservice-tester </Location>, <Location /AssertionManagerGui> ProxyPass ajp://127.0.0.1:8538/AssertionManagerGui ProxyPassReverse ajp://127.0.0.1:8538/AssertionManagerGui </Location>, <Location /GazelleFhirValidator> ProxyPass ajp://127.0.0.1:8515/GazelleFhirValidator ProxyPassReverse ajp://127.0.0.1:8515/GazelleFhirValidator </Location>, <Location /FhirValidator-ejb> ProxyPass ajp://localhost:8515/FhirValidator-ejb ProxyPassReverse ajp://localhost:8515/FhirValidator-ejb </Location>, <Location /EVSClient> ProxyPass ajp://127.0.0.1:8514/EVSClient ProxyPassReverse ajp://127.0.0.1:8514/EVSClient </Location>, <Location /sso> ProxyPass http://localhost:8599/sso ProxyPassReverse http://localhost:8599/sso </Location>, <Location /gazelle-documentation> ProxyPass http://127.0.0.1:8584/gazelle-documentation ProxyPassReverse http://127.0.0.1:8584/gazelle-documentation </Location>, <Location /proxy> ProxyPass ajp://127.0.0.1:8524/proxy ProxyPassReverse ajp://127.0.0.1:8524/proxy </Location>, <Location /gazelle-proxy-ejb> ProxyPass ajp://127.0.0.1:8524/gazelle-proxy-ejb ProxyPassReverse ajp://127.0.0.1:8524/gazelle-proxy-ejb </Location>

And I try to perform the same operation but it’s failed:

REGEX_REPLACE({Apache2 configuration (from Applications endpoint) (from Application name) (from Application list) (from Customers environments)}, ",", "\n")

The output:

#ERROR!

Any idea to help me ?

Thanks you :slightly_smiling_face:

[kuovonne]

Benjamin_Carriou:

I have a lookup field

This is the issue. Lookup fields are not text strings, and the commas are not really there.

You can use a rollup field instead of a lookup, or you can turn the lookup into a string in your formula by tacking & "" after the field name.

[Justin_Barrett]

@kuovonne’s suggestion of a rollup sounds like it’s what you want considering that you want each item on its own line. For the rollup’s aggregation formula, use the ARRAYJOIN() function to join all items with “\n” as the separator.

[Benjamin_Carrio]

Thanks @kuovonne and @Justin_Barrett for your help !
I have test your two solutions and it’s works perfectly !

Have a good day !

[Karlstens]

Messing about with Regex, could anyone assist with allowing brackets to parse as string data?

For example;

"Tasks" Text Field

"[x] Text Tick Box item 1
[x] Text Tick Box item 2
[ ] Text Tick Box item 3"

This works ok;

IF(FIND( "[ ]",{Tasks},0), "Incomplete", "Complete")

But Regex_Match needs extra formatting to work that I need a hand with;

IF(REGEX_MATCH({Tasks}, "[ ]"), "Incomplete", "Complete"))