Hey there stranger!
Sign up to get access.
REGEXMATCH Formula Generator for Google Sheets
About this Tutorial
0:11 But here, let me explain what it is. So rejects, or regular expressions, is sort of a code like syntax that you can use for pattern matching.
0:22 Basically, in any text or any string of characters, you can say, hey, I wanna look for this kind of, thing, you can use rejects or regular expressions to, to find a pattern.
0:38 I have here 200 examples. I'm gonna go over the examples in another video very specifically and you'll be able to get this sheet.
0:44 Actually, if you're on betterseeds.co right now, you can get the sheet below and in the other video, it'll be the same exact sheet.
0:50 But we're just gonna cover a little bit of what Regex is here and why I built this Regex sort of formulas don't do which is it just, and it's very simple.
1:09 It returns true or false. It's finding a match and if it doesn't find a match, it's false. Finds a match?
1:14 True. So it's really useful for data validation. If you're just validating, does something exist? Or if you're like, hey, I have all this stuff. All this text and I want to know where it is and I want to get it rid of it.
1:25 Like I just want to know true false. Is it there or not? Maybe you want to clean some data. You're like, hey, I'm looking for like all these at mentions and I want to take those out of the text or hashtags.
1:34 I want to take out anywhere there's hashtags. You can clean data really easily if you just do true or false.
1:40 You're like, hey, all the false or all the trues, I want to get rid of. But data validation is you want the true.
1:45 You want true and you're like, does this have this text pattern or not? Data filtering, if you're like, hey, I want to know, you know, in all of these, these reviews, is there a URL?
1:57 Is there in all of these sort of sets of texts and all of these tweets? Like maybe I exported all my tweets and I want to know where do I mention other people?
2:04 Data classification is really good for a Rejects match because Rejects Fest, again, is just returned. It can be combined with if, which needs a logical expression of true or false, and then based Based on the outcome, you can classify your data.
2:22 Error checking, too, if you find that you're like, oh, I have like a few. URLs, but I want to make sure they're all formatted correctly.
2:31 Where are they? Where is it false, right? Where did I make the error? If you're using conditional formatting a lot, sometimes you need to just say, hey.
2:42 In all of these texts, I want to find where there is two words in the mat that, like, cat or dog.
2:49 And sometimes conditional formatting can be done, you can use rejects or regular expressions inside of conditional formatting, like, I don't want to do it outside of the sheet, I want, like, not outside the sheet, but outside the text, I want to have a column that says true, false, true, false, and then
3:06 I'll do conditional formatting where it's just, if this column is true, highlighted, or if this is false, highlighted, this is really helpful.
3:14 Rejects my match of You might use this in another video, but right now we're just gonna go for with through a couple Because I want to tell you why I built this rejects matcher, which is I Got over 200 examples here, but that's not the end.
3:44 There are some there an infinite amount of use cases for this and you'll see like when look for URL URL detection Just if there is a URL or not, but sometimes like video URL there's social media profile URL Twitter URL detection Instagram Here's all the URL all these different types of things.
4:04 They have relatively the same rejects expression that you'll need but based on maybe you want Wikipedia or not. Okay, that's why I built rejects matches to get exactly your use case.
4:19 So if we want to do like Instagram, like we just want to say, is there an Instagram URL here on better sheets dot co slash reject rejects matcher completely free tool.
4:31 I'm going to enter your use case here. I want to verify if a. Style contains a valid Instagram profile URL and I'm actually going to pick this text to match and just like put whatever text you want to match in in it right here's the URL.
4:50 We'll be right back. You could, and I'll show you there is a little bit of an error here, but it's not an error.
4:58 It's just something that doesn't work or go. But basically I want to also say I want to get the exact formula.
5:06 You're going to get out of it. This is the exact formula for Reject Match. So over here we have it already.
5:12 I have checked all of these off and it's going to say true. But let's use this. Where is it? D-40 is the text I'm looking for.
5:22 So I'm going to enter D-40. And if you don't enter anything it'll just do, it'll just put A1. But we'll get Reject Match.
5:28 It'll generate now the Rejects. So I'm going to copy, it's actually copied to my clipboard already. I don't even have to copy it.
5:37 And let's paste it in here. I didn't write it now. I just used the Reject Matchmaker and it exactly got it right.
5:46 This is true. Meaning if this has Instagram.com in it, if it doesn't, it's false. So it didn't find Instagram. But if it has Instagram.com and some profile information, it'll say true.
6:02 That's pretty cool, right? And we can use any use case here. You can verify if a cell contains an email address.
6:11 And we're gonna be like, this is my email. Andrew at better sheets dot go and we'll just do a one here get the rejects for that and it will automatically generate exactly that this is the rejects for email address, right?
6:29 You can sort of see it if you know a little bit about rejects or regular expressions. Thing is, I don't know that much about rejects.
6:36 I can sort of figure it out pretty by reading all of these things, right? The using exactly the character. We have A to Z in brackets to say, okay, any letter A to Z, and you, but you have to do A to Z lowercase and A to Z uppercase, and there's a lot of reject stuff that I don't remember.
7:00 And so reject this, I built actually for myself to make sure that I got the correct rejects on regular expressions because I was using rejects a lot to like do matches and find certain words and like do things like palindromes and stuff.
7:16 What I will mention to you, there is one little thing you need to know when you're using this tool, and let's see if it happens here.
7:24 It doesn't, I think it's gonna maybe be fixed cell, verify if text is an email address. Let's see if that changes it.
7:35 I want to show you one weird thing that could happen, and it's not happening, but basically I'll explain it. In Rejects, there is a couple of characters that you can add to this where, let's see here.
7:49 At the beginning, if we use this carrot, upper carrot, and if at the end we use a dollar sign, this is going to be false, even though it's actually true.
8:02 It should be true, right? We're looking for a Twitter profile. Extra characters mean that it's at the beginning or the end of the text.
8:11 So this dollar sign here, if you get this dollar sign, and if you get a carrot, this sort of upper carrot thing, make sure you delete those.
8:20 It might end up being false in your rejects match and you're like, hey, this actually should work. But if you see this dollar sign and you see this upper carrot in the beginning, just, you just have to delete them and it'll end up being true.
8:33 It's because it's locking it to the beginning or end of the text. That's what those characters do in rejects. And sometimes a rejects matcher will add those there because sometimes the text that you add, it thinks that's the entire text kind of thing.
8:49 So hopefully this is really, this is really helpful to you. It's really helpful to me already for a lot of these use cases.
8:57 When I get really dicey rejects that I need and I need to do a lot of data validation, cleaning data, data filtering, data classification.
9:04 I want to do some error checking on text. I want to make sure, you know, mentions are here or mentions are not here.
9:09 And really helpful for conditional formatting. Again, in another video I will go through a ton more use cases. I have 200 examples here.
9:17 You can get the, if you're a BetterSheets member now, go grab the sheet. It's at BetterSheets.co. And, if you are not a member and you're watching this, say, on YouTube, check out BetterSheets.co.slash-rejects-match-er and it's a free tool to create the Rejects Match formula for you and for your specific
9:36 use case. Alright, bye.