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!
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
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!
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.