Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Regex Match or Extract - Similar string/phrases not working, works on regex101

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

I’m trying to extract variable but similar strings of text. They all start with “PARVOVIRUS” and end with “=”.

When I build this on regex101.com it works fine. I can extract it multiple times from a long list:
Expression:
(?>PARVOVIRUS).*?(?=1)

However, when I put this back into Airtable I get #ERROR!

REGEX_EXTRACT({XmlData}, ‘(?>PARVOVIRUS).*?(?=1)’)

The similar strings are:

“CANINE PARVOVIRUS IFA SEROLOGY TITRE: =1:20”

AND

“CANINE PARVOVIRUS IFA SEROLOGY TITRE: >=1:80”

I can’t work out what I am doing wrong, and why airtable rejects it.
Ultimately I am just trying to extract that 1:20 or 1:80 value but it’s variable length if “&gt” >= is there or not in the text.

2 Replies 2

I think the closest flavour match to Airtable is Golang so you need to make sure your regex is working on that first I think?

From what I understand, Airtable’s REGEX_EXTRACT() also only extracts the first match, so that may also be an issue even if you solve the above

Take whatever I say with a huge amount of salt though; I’m at whatever skill level is before “Beginner” for regex heh

Hi @Stephen_Joslyn

Is the length of the segment you want to extract always that same length/format? ( 1:20 & 1:80 are both 4 characters )

If so, you could get that more simply like this:

IF(
  {XmlData},
  RIGHT({XmlData}, 4)
)

Otherwise, the next thing I’d ask is, are you copy-pasting your regex into the formula field? If so, check that your quotation marks are not “curly” quotes, but rather “straight” quotes. Airtable’s formula field doesn’t like curly quotes and will error your formula because of them. Copy-pasting a string with quotation marks into the formula field editor is usually going to paste them as curly quotes, so just delete and re-type any quotation marks and see if that makes a difference.