Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 07, 2022 05:05 PM
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.
Solved! Go to Solution.
Mar 08, 2022 06:07 AM
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.
Mar 07, 2022 09:36 PM
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.
Mar 08, 2022 05:22 AM
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.
Mar 08, 2022 06:07 AM
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.
Mar 08, 2022 07:05 AM
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.