What formula do I need to extract this text?

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')

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().
2 Likes