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!

[TheTimeSavingCo]

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', 
  ''
)

[Rachel_Braedyn]

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.

[TheTimeSavingCo]

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?

[rbautomate]

I just laughed until I cried at this. Feels very very true in my soul. RegEx 🤷‍♀️