Extracting text string that does NOT contain certain text

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!

I believe this is your answer -

1 Like

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.

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

Famous quote by me:

No single person on this planet understands all of RegEx.

2 Likes

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!

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!

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?

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.

1 Like

Oof, you’re right. Thanks once again

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

Screen Recording 2022-08-24 at 9.21.27 AM

Sorta useful I guess

Yeap, and after incorporating the fixes mentioned by kuovonne, you’d end up with:

REGEX_REPLACE(
  REGEX_REPLACE(
    {Attendee email list}, 
    '[^,]+@group.calendar.com', 
    ''
  ), 
  '[^,]+@company.com', 
  ''
)

Thanks so much for the help! It seems to work perfectly except for 1 thing: If the company email address is not the first email in the list of populated emails, it also erases external email addresses before it, leaving a blank result.

Hi Rachel, hmm, that’s an odd one and I can’t seem to replicate that

Any chance you could invite me to your base so that I can troubleshoot it there?

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.