Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

What formula do I need to extract this text?

Topic Labels: Formulas
Solved
Jump to Solution
214 2
cancel
Showing results for 
Search instead for 
Did you mean: 

I have a field called: Branch Name
I need to extract text from it using a formula field. I’m just not sure how.

Examples of data in the field:
#2828 - Clarksville Branch - Clarksville, TN
#4109 - Aiken Branch - Aiken, SC

What I need to extract:
Clarksville Branch
Aiken Branch

I tried this formula, but it doesn’t work (returns #ERROR!) and I don’t know enough about REGEX to troubleshoot:
REGEX_MATCH({Branch Name}, '/(?<=-).*(?=-)/g')

1 Solution

Accepted Solutions

Here’s a Regex solution:

IF({Branch Name}, TRIM(REGEX_EXTRACT({Branch Name}, " (?:\\b)([^-]*)")))

Screen Shot 2022-05-04 at 8.21.22 PM

A couple of Regex tips:

  • You don’t need to wrap the regular expression in forward slashes. That’s only for some interpreters (e.g. JavaScript), and the one used by Airtable doesn’t support that.
  • For testing, I recommend regex101.com . On that site, pick the Golang variant on the left before testing your expression, as that’s the interpreter that’s closest to what Airtable uses. Once your expression works, just copy the text and paste it into a string in the proper Airtable function. No extra wrapper characters needed, and Airtable will automatically add extra backslashes to escape any that are part of the expression.
  • The REGEX_MATCH() function only tells you if there is a match in the string. To extract, use REGEX_EXTRACT().

See Solution in Thread

2 Replies 2

Hi @Cady_Smith1
REGEX will certainly be more concise but I dont know REGEX. Here is a formula that will work assuming the data is always formatted the same.

LEFT(MID(TRIM({Branch Name}),FIND("-", TRIM({Branch Name}))+1, LEN(TRIM({Branch Name}))),FIND("-", MID(TRIM({Branch Name}),FIND("-", TRIM({Branch Name}))+1, LEN(TRIM({Branch Name}))))-1,LEN(MID(TRIM({Branch Name}),FIND("-", TRIM({Branch Name}))+1, LEN(TRIM({Branch Name})))))

No doubt there is a better way to do it though.

Here’s a Regex solution:

IF({Branch Name}, TRIM(REGEX_EXTRACT({Branch Name}, " (?:\\b)([^-]*)")))

Screen Shot 2022-05-04 at 8.21.22 PM

A couple of Regex tips:

  • You don’t need to wrap the regular expression in forward slashes. That’s only for some interpreters (e.g. JavaScript), and the one used by Airtable doesn’t support that.
  • For testing, I recommend regex101.com . On that site, pick the Golang variant on the left before testing your expression, as that’s the interpreter that’s closest to what Airtable uses. Once your expression works, just copy the text and paste it into a string in the proper Airtable function. No extra wrapper characters needed, and Airtable will automatically add extra backslashes to escape any that are part of the expression.
  • The REGEX_MATCH() function only tells you if there is a match in the string. To extract, use REGEX_EXTRACT().