Help

Extract text from dynamic string

Topic Labels: Formulas
734 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Burner
7 - App Architect
7 - App Architect

Hi,

I’m trying to come up with a formula that extracts the first name, last name, and email that I’m getting into Airtable from another application. The format of the UID I’m receiving is shown in the couple of examples below:

John_Smith_john@example.com
Tracy_Jones_tracy@example.com

Basically, it’s going to be first name underscore last name underscore email.

I need to write three formulas, in my Table for the First Name, Last Name, and Email columns. I’m not sure how to proceed especially because the UID string length will be dynamic based on users’ names and emails, so I can’t use a LEFT or RIGHT formula to count and extract the text. I know there should be a way to do this, but could really use some help with the formulas.

Thank you.

1 Reply 1
augmented
10 - Mercury
10 - Mercury

Hi. This is only one way. There are others that may be better. You can probably do without the conversion of ‘_’ to a whitespace but I’m not that good at regular expressions.

First REGEX_EXTRACT(SUBSTITUTE(UID,'_',' '),'^(\\w+\\s){1}')
Last REGEX_EXTRACT(SUBSTITUTE(UID,'_',' '),'^(\\w+\\s){2}')
Email REGEX_EXTRACT(SUBSTITUTE(UID,'_',' '),'([a-zA-Z0-9._-]+@[a-zA-Z0-9._-]+\\.[a-zA-Z0-9_-]+)')

Good luck.