Your Spreadsheets Are Leaking Email Addresses (Fix It in 30 Seconds)
Learn how to quickly redact email addresses from your spreadsheets using regular expressions in Google Sheets. This video provides a step-by-step guide to ensure your data remains private while maintaining its context.
So, you want to redact your email addresses . You want to remove email addresses from some information. We want to hide specific contact points. We want to show data without exposing personal information. Or we want to redact emails from logs. Maybe we want to handle complex addresses that have numbers, symbols, whatever. Or we want to be keeping timestamps and context, but we want to just hide very literal email addresses . Email addresses are really funny because it's a bunch of text wrapped around the at@ symbol and we have text and a dot and then text because we want to accept anything that's like a.com cafe.game like.net.co. So let's do this. I want to show you the little code we do, but also I'm going to How To Get Domain from Emails
show you how to use reject replace because we can use equals reject replace. It's one of three regular expression formulas. Can do reax match which says hey does this exist. So it's just going to give us true or false. We can do extract and say just get the email address. Or we can use reax replace to say hey if you find the email address replace it with something else. That something else is just going to be some asterises and a little it could be any text you want bunch of x's if you want. So we're going to use reax replace. And we need to use the text in this case in B3. In your case, it might be somewhere else in your sheet, might be in another column somewhere else. Then we're going to need a little bit of a code. And that code is going to be the REGEXMATCH Formula Generator for Google Sheets How to Validate Email Addresses for FREE in 17 Minutes Replace Numbers with Asterisks
structure of an email address, but in regular expressions , and we're going to wrap that in quotes at first. So, we're going to have like three main things. We're going to have the name in an email address, the at symbol, then the domain, and then a.com or dot something an extension. And there's two items that separate these, right? It's the at symbol and a dot. So those two items are going to separate these three things. The last part actually, funny enough, is really easy. Let me go through that real quickly. And that's just in square brackets a hyphen z. And we need both the uppercase and lowerase. And then we need to put in curly brackets the number two. So this ensures that there's at How to Validate Email Addresses How To Get Domain from Emails
least two numbers. So if there's a dot b or c kind of thing, we don't that's not an email address. We're actually looking only for real email addresses here. And that two in curly brackets says we need at least two letters. Okay, between the at and the dot, we could have any letters. So, we're going to put in square brackets a hyphen Z also capitalized, but we could also have 0- 9, which is 0 through 9. There's a couple more symbols like dot and a hyphen. And that's it for the domain part. And before the username, there's a few more things we need to take care of. Oh, also just before I forget, we're at How to Validate Email Addresses How To Get Domain from Emails
a plus after the square brackets. And that means that's going to be more than one or more characters before the at sign. We're back to whatever is this name. There's a few things that could be a possibility here, but we're going to put everything inside of a square bracket. We're going to add that plus just like we did with the do uh the other side. We're going to do a hyphen z capital a hyphen capital z. We're gonna do zero hyphen 9. Replace Numbers with Asterisks
We're going to add a percent sign. We're going to add a dot, an underscore, and plus and hyphen. So this says all of these characters here. This says this plus sign means any other kind of characters. But we want to do one more thing which is add a backslash between the plus and the dot here because we want to use this dot as a literal character. So let's see how that works. We'll need actually one more thing which is after all of this. We want to say hey replace it with something. So, we're going to replace it with three asterises at three asterises dot three asterisks. We need to add a comma between the two and the curly brackets here because right now it's just getting only two Ken Asks: Can We Add Star Ratings? Replace Numbers with Asterisks
characters, but we want it to get two or more characters. And now we can see that this works. So, we've created the reax for before the at, after the at, and after the dot. And we're replacing all of that anytime we find that pattern with this sort of anonymized piece of information. We could wrap the whole thing with array formula . Change B3 to B3 col B and we get every single one right here as an example. Pretty cool, right? You're watching Better Sheets here on YouTube. Make sure you check out this video or this video and subscribe right now to get more tips, tricks, how-tos, get more out of your Google Sheets than you ever have before. I'm excited to be making a ton more videos here. Ask me questions down in Better Sheets Videos Checklist Your First Formula in Google Sheets Replace Numbers with Asterisks
the comments and I will answer them in future videos. But for right now, right here, one of these videos is going to be your next Google sheet. Google Sheets Stories? No! But we'll add timestamped video notes to your google sheets. 80 Years In 1 Spreadsheet Google Sheets Stories? No! But we'll add timestamped video notes to your google sheets. Download Google Sheets