Help

REGEX_EXTRACT Help

Topic Labels: Formulas
Solved
Jump to Solution
1936 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Fred_Smith1
5 - Automation Enthusiast
5 - Automation Enthusiast

I am trying to use REGEX_EXTRACT to convert a username into an Email address. For example: WSSUMITS\smithfred => smithfred, then I can add the @domain.com to the result.

The regex formula I came up with, through trial and error, is:

REGEX_EXTRACT({Asset Last Logged In User}, '^(?:WSSUMITS)?([^:\\/\n?])+\\')

However, I’m getting ERROR in the field. I don’t have much experience with regex. Where am I going wrong?

Thanks in advance.

1 Solution

Accepted Solutions

Those regular expression testers don’t use the exact flavor of REGEX that Airtable uses. Usually this doesn’t matter, but apparently this time it does.

I also wonder if you might be using REGEX_EXTRACT when you want REGEX_REPLACE. Although you will need to get the right pattern in either case.

See Solution in Thread

4 Replies 4

Welcome to the Airtable community!

REGEX_EXTRACT produces an error if a match is not found. Here are some possible reasons:

  • The {Asset Last Logged In User} field is a lookup field and is returning an array instead of a string.

  • The regular expression isn’t actually the correct regular expression to return what you want.

Regular expressions can be tricky to write and a pain to debug. Do all of the inputs always start with WSSUMITS\? If so, you could probably use a simpler regular expression, or maybe even a simple SUBSTITUTE formula.

Finally, this support page has some useful links about regular expressions.

I double checked and the Asset Last Logged in User is a string. All users are recorded as domain\username. When I test the regex in regex101 or RegExr, it gives me exactly the output I’m looking for. In fact, I used the example that is given in Airtable help docs, modified of course. The base itself is imported from an Excel file. I don’t know if that matters. Unfortunately, the report I’m pulling from does not record the user’s email, which would make this matter trivial.

msedge_Di97tETu0E
msedge_GhndQyOYJl

Those regular expression testers don’t use the exact flavor of REGEX that Airtable uses. Usually this doesn’t matter, but apparently this time it does.

I also wonder if you might be using REGEX_EXTRACT when you want REGEX_REPLACE. Although you will need to get the right pattern in either case.

That suggestion helped a lot. I didn’t use REGEX_REPLACE, but I did use REPLACE. For example:
Input: {Asset Last Logged In User} = WSSUMITS\smithfred
Formula: REPLACE({Asset Last Logged In User},1,9,"")
Output: {Domain removal} = smithfred

Then I can run a shorthand version of concatenate:
{Domain Removal}&"@domain.com"
Output: {Email} = smithfred@domain.com

Trying to use regex was making it more complicated than necessary. Which is way I try to stay away from it.