REGEX_EXTRACT Help

[Fred_Smith1]

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.

[kuovonne]

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.

[Fred_Smith1]

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.

[kuovonne · Accepted solution]

Fred_Smith1:

When I test the regex in regex101 or RegExr,

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.

[Fred_Smith1]

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.