Extract text from dynamic string

[Burner]

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.

[augmented]

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.