Skip to main content

Being no formula expert, I am assuming that the new REGEX_EXTRACT formula function would be a good way to achieve the following.


I want to extract three digits from a name field. These three digits are always in the same place after an underscore.


Name Field: DAM301_010 POWERHOUSE

Extraction Formula Field: 010


Name Field: DAM204_115 DROP THE BALL

Extraction Formula Field: 115


I apologize if this seems extremely simple and obvious to the pros, but for a formula layman, it’s anything but. :winking_face:


Thank you so much in advance.

In Airtable, regular expressions cannot do “look behind” matches, so you need to extract the underscore character along with the digits. Then you can remove the _ character. You can also use REGEX_MATCH to test if the pattern is found to avoid #ERROR! when there is not match.


Note: this formula will extract all the digits immediately following the underscore, not just the first three.


IF(

  REGEX_MATCH({Name Field}, "_\\d+"),

  REPLACE(

    REGEX_EXTRACT({Name Field}, "_\\d+"), 

    1, 1, ""

  )

)

In Airtable, regular expressions cannot do “look behind” matches, so you need to extract the underscore character along with the digits. Then you can remove the _ character. You can also use REGEX_MATCH to test if the pattern is found to avoid #ERROR! when there is not match.


Note: this formula will extract all the digits immediately following the underscore, not just the first three.


IF(

  REGEX_MATCH({Name Field}, "_\\d+"),

  REPLACE(

    REGEX_EXTRACT({Name Field}, "_\\d+"), 

    1, 1, ""

  )

)

Thank you so much, @kuovonne

Would this formula do the same thing?


REGEX_EXTRACT({Name Field}, “\d+\b”)

Thank you so much, @kuovonne

Would this formula do the same thing?


REGEX_EXTRACT({Name Field}, “\d+\b”)



Probably. You can easily test it for yourself. I don’t remember if the underscore counts as a word boundary or not for a regular expression.


There are many ways to do things with code. Use what works for you.


I realize that. Thank you again for your help, @kuovonne

Reply


Terms & Conditions