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

Need help with regex extract

299 4
cancel
Showing results for 
Search instead for 
Did you mean: 

I have a regex that i tested with Regex101 and it worked.
Purpose of this regex is to extract the remaining words after “XXXXX-YYY-ZZZZ”

image

But in airtable i got #Error!

Number 1. Extracted a pattern
image

Number 2. I constructed regex exactly the same as that in Regex 101
image

Number 3. I used this formula
image

What am i missing?

4 Replies 4

It looks like you’re using the wrong REGEX variant on Regex101.com, so you’re probably using some invalid tokens. The variant that Airtable uses is closest to the Golang option on the site. You can partly tell the difference by the prefix in the “Regular Expression” entry field.

I also think that you’re making the expression much more complex than it needs to be. Instead of looking for the part number specifically, notice how there’s a space after that part number right before the piece you want to extract. Forget everything before that first space and just pick it up everything after it. Try this:

TRIM(REGEX_EXTRACT({Name from Accurate}, " .*$"))

The $ token at the end tells the interpreter to focus the search at the end of the string, which lets us easily find a single space and everything after it, which should get you what you want. While the expression could be written to specifically ignore the space, it’s easy enough to just trim it after the fact.

Thanks Justin.

Sorry, i have to change the source text into
“.[C] 12251-RB0-004A Packing kop asbes”

Noticr that know i have a space before part number.

Any idea how can i extract all words after the part number?

Try this variant of the formula. I also wrapped it in an IF() function so that it won’t throw an error if the {Name from Accurate} field is empty.

IF({Name from Accurate}, REGEX_EXTRACT({Name from Accurate}, "(?:-.{4} )(.*)$"))

Long story short, this also targets the end of the string, starting by looking for the last four characters in the part number—assuming that the end of the part number will always be four characters after a hyphen—and grabbing the rest. Because this variant specifically targets and ignores the space after the end of the part number, there’s no need to trim the result.

Screen Shot 2022-07-01 at 3.50.53 PM

Justin,

I found more robust solution

REGEX_EXTRACT({Name from Accurate},"\\s\\S*\\s(.*)")

Thank you for inspiring me!