Extracting text string that does NOT contain certain text

[Rachel_Braedyn]

I have an automation set up that loads Google Calendar meeting info into my database, including attendee email addresses.

What I want to do is extract the emails of only the people who are not internal employees. That is to say, extract all emails in the “Attendees” field that do not end in “@company.com”. External participants could have any number of email domains and I want to be able to extract any and all email addresses of Google meeting attendees who are not from our own company. I hope that was clear. Thank you!

[Bill_French]

I believe this is your answer -

New: REGEX formula functions Announcements

[regex_functions] Hello everyone! We recently launched some exciting new additions to our formula field with three new regex (regular expression) functions: REGEX_MATCH(string, regex) REGEX_EXTRACT(string, regex) REGEX_REPLACE(string, regex, replacement) These functions can be used to match character combinations within text strings. For those interested, Airtable’s REGEX functions are implemented using the RE2 regular expression library. You can learn more about these functions in this ar…

[Rachel_Braedyn]

I seem to understand how that pulls data for that particular string but want I want to do is, in a separate column/field, is list the participants whose emails are not from the company domain.

[TheTimeSavingCo]

Yeap, and you can use the REGEX_REPLACE() formula to do what you’re looking for after you get the expression sorted like so:

Here’s the formula I used:
REGEX_REPLACE(Name, '[^,]@company.com', '')

I’m not entirely sure what I’m doing with regex and it’s pretty much magic to me, so apologies if it doesn’t work as intended. It’s got some leading and trailing comma issues, and those can be dealt with via some IF()s or more regex if needed I reckon

[Bill_French]

Adam_TheTimeSavingCo:

I’m not entirely sure what I’m doing with regex

Famous quote by me:

No single person on this planet understands all of RegEx.

[Rachel_Braedyn]

I appreciate the help and it seems to have worked so thank you.

However, I realized there’s a. second email domain I also want to filter out. So I want to filter out those ending in “@company.com” and “@group.calendar.com”. Is there any way I could filter out those 2? Thanks!

[TheTimeSavingCo]

Hi Rachel, apologies for the late reply, could you try this?

REGEX_REPLACE(
  REGEX_REPLACE(
    Name, 
    '[^,]+@group.calendar.com', 
    ''
  ), 
  '[^,]+@company.com', 
  ''
)

---

Edited to fix backticks issue!

[Rachel_Braedyn]

Hi Adam, I used your formula and unfortunately got an error message saying “Invalid formula”. I replaced Name for the field/column name I want to reference, “Attendee email list”. Is that correct?

[kuovonne]

Rachel_Braedyn:

I used your formula and unfortunately got an error message saying “Invalid formula”.

Adam accidentally has an extra set of three backticks (```) at the end of his formula after the last closing parentheses. You should remove them.

Be sure to enclose your field name in curly braces since you have spaces in your field name.

[TheTimeSavingCo]

Oof, you’re right. Thanks once again

While fixing that, I also learned that code blocks just close themselves apparently

Sorta useful I guess