RegEx as formula

[adrrs]

Hello community!

Perhaps a simple problem, but I was unable to resolve this so am seeking your help. I have setup a column "RegEx Schema" that describes the schema that I want to apply to extract a named group from a html block.

I have entered the formula as such but am getting all 0 values.

Any help on how to rectify it would be greatly appreciated.

Thanks!

[SamDesk]

Hi @adrrs,

If you're looking to extract text from a string using RegEx, then you should use REGEX_Replace instead of REGEX_Match. REGEX_Match will only show you if a string matches a RegEx expression with a 1 or 0.

Also, the way you're using REGEX_Match would never work if you're looking to match a partial string from a larger string as it will only return 1 for a complete match. To use it to find the string you're looking for, you would need to add ".*" to the beginning and end of your "RegEx Schema" variable.

".*"+[RegEx Schema]+".*"

 So in full, your "Value" column formula would look like this:

REGEX_Match([DownloadData],".*"+[RegEx Schema]+".*")

This will now look in DownloadData for a record that contains the pattern described by "RegEx Schema", contained with anything else before or after it - ".*". 

Hope this helps.

Sam 🙂

[adrrs]

Hi Sam,

Many thanks for your response. I was thinking of Regex_Replace but wasn't sure what I was replacing the scheme with. Could you elaborate on how the Replace function would work if I had to extract text rather than replace text?

Thanks!

[SamDesk · Accepted solution]

Hi @adrrs,

Certainly. REGEX_Replace takes 3 parameters; the string you want to search, the pattern to find what you want to replace and finally what you want to replace it with.

In your case the formula would look like this:

REGEX_Replace([DownloadData],".*"+([RegEx Schema])+".*","$1")

Notice here how I've added brackets () around "RegEx Schema", this is called a capturing group in RegEx. We can call these capturing groups to extract specific string patterns from a larger pattern. "$1" is calling the first capturing group in our pattern, which also happens to be the only capturing group but you can have multiple.

So within "DownloadData" we're looking for a record where "RegEx Schema" is contained somewhere within it, then choosing to replace the whole record with just the matched pattern from "RegEx Schema".

Sam 🙂

[adrrs]

Perfect! Thanks, Sam!