Hey there stranger!

Sign up to get access.

200 REGEXMATCH Examples in Google Sheets

About this Tutorial

Uncover the Magic of Regex Match! ๐ŸŽฉโœจ Explore 200+ Examples at BetterSheets.co. Validate emails, detect hashtags, format data & more! Dive into the world of Regular Expressions. Start Your Regex Journey Now!

Use REGEXMATCHER to generate any REGEXTMATCH formula you want.

Video Transcript

00:00 Hi, in this video we're gonna go over 200 examples of Regix match. Actually you're going to get 200 examples of Regix match You're on bettersheets.co down below you get the sheet you can get this sheet exactly I think this tutorial is gonna have three parts one first off I want to make sure you know 
00:18 that there is the Regix matcher bettersheets.co slash Regix matcher so anytime you have a use case that you're like I don't know if it's in here you can definitely like search through here and look through this examples but you can also just go to bettersheets.co slash Regix matcher put in your use case
00:34 put in the text that you want to match and then sell and it will generate the Regix match formula for you including the Regix the regular expressions so I want to introduce that to you so we're gonna go over why we would use Regix match we're gonna talk a little bit about regular expressions in while
00:51 we do this and then I'm going to go through some examples and the third part is we're actually going to do a little bit of Regix and we're going to be able to read a little bit I'm going to try to teach you a little bit about reading regular expressions because with this Regix match here that's powering
01:07 our Regix match now and these examples you have the Regix already written for you there come there's going to be two problems one we don't know how to generate Regix ourselves maybe but the second problem is even if you do know how to generate it if you don't know how to generate it but you still want
01:27 to use it and say you use an example or you use Regix match match or the matchmaker you might not know how to read it and you might only need a couple of little edits to exactly use your use case and you can learn.
01:42 I think this is a really cool way to learn regular expressions by seeing all of these examples and over the course of all of these I hope that you'll be able to you know infer a lot of information but I will go through in this video at least one of these sections will be generally just going over regular
01:58 expressions and editing this and seeing it in action so you'll see exactly how to use it. So let's talk about why we use Regex match.
02:06 Regex match is a formula in google sheets that does one thing. It matches any regular expression we give it to the text and it will result in either true or false.
02:19 Absolutely also there's errors, you know, there it can come up with an error. One of the errors I commonly come up with I'm find myself in is this is not a regular expression or it's like not the correct syntax and then I go to a reject checker which you can go to a reject checker like google it and 
02:37 you can see you can copy and paste regular expressions into here and see if it actually is you'll go a real regular expression but in google sheets it'll give you an error, a reject match will give you an error like this is not a regular expression you're like putting me in here you're like it is so 
02:52 I want to tell you a couple of findings I found one is google sheets regular expressions does not do backwards looking or forward looking regular expressions so there's backward looking regular rejects look ahead or look behind it does not do these so if rejects match or the match makes or generates 
03:15 any rejects with positive look ahead or backwards look behind any of this it will not work in google sheets but it is actual rejects so you'll get that you may get at that error and all of these examples that i have none of them have an error you'll see true always over here right above my head you'll
03:37 see all truths no errors but that's also because i've edited it so that sometimes the error is like you don't need that look behind you don't need the look ahead you don't need that particular thing or it's a use case that's extremely weird like most people don't use it i mean a lot of these use cases
03:57 are are quite weird and in fact that's sort of the beauty of regular expressions and rejects matching Thanks for watching.
04:02 Rejects replace and reject extract is that like these formulas have been built and used in Google Sheets because there's an infinite number of use cases and this was really exciting okay so why do we let's go actually so that was the syntax you know the Thank you.
04:18 Thank you. About rejects match a little bit that there's going to be a little bit of syntax right this is a go over it it's two things it's going to take in the text you're looking through and then the second thing is the regular expression that's it just those two things and it's going to return true
04:34 or false. So why do we use it actually let's get to this point data validation validate your data is the is the data the text that we have matching to a pattern we want is it a URL is that URL correctly created is it an email address do we actually have an email address.
04:51 In this text or does it look like it or is it a big amount of text and we're looking for say the word cat or dog or cat or dog as you'll see one of these examples we may want to clean our data meaning we have a lot of instances of data like maybe thousands of tweets and we're looking only for very specific
05:11 things or we want to take care of errors if we have misallocated or mistyped things and we want to make sure every one of these pieces of text are true that it has this thing and if it's false we're going to like oh we have to go edit that we need to clean that data data filtering if you're like hey 
05:28 i want to get out of this list the things that start with a bullet point or the things that have a weird character or things that are not a sentence they don't have a period at the end i want to go and filter all of those data classification if you're using rejects match and you just want to know you
05:48 know does this data have this or that this or that if is really great for that because if needs a logical expression needs true or false and so rejects match the output is true or false really great for that error checking i want to make sure you know that my data has x or my data doesn't have y something
06:08 right conditional formatting so you can use regular expressions in conditional formatting conditional formatting is extremely useful we do we use conditional formatting all the time right and most of the time we're like on those edge cases we're like but i want to know if the word this particular word
06:26 is in here and we're going to use regular expressions inside of conditional formatting but the cool thing about rejects match right is that we can set our conditional formatting to one column and say true or false and then do use rejects match to figure out if this has it or not if this text has it or
06:44 not that's really cool. Yeah, let's go through some examples. I bet you're dying to see some examples. Let's go through the very first one.
06:50 Email validation I want to know does this text have an email address maybe we're scraping from a ton of websites when we have hundreds of websites and we're like we just want to know if there's an email address in here right we may be creating a PR list of a public relations list we may be creating lists
07:06 for bloggers to youtubers to anyone we're scraping like tons of channels descriptions and we're like in all of these YouTube descriptions they're an email address and we want to know if it's just true or false and then we'll go in and look at that email address right you can also use rejects extract 
07:23 if you're like I want to extract all of these but this one we're just using match and so what we have is a text and matches just like John Doe at example.com could be anything Thanks for watching!
07:32 And here's the regular expression this is the regular expression so let me show it to you in rejects match we have I pulled out the rejects over here in column E but I want to type this for you just so you see how it works completely so we're gonna do rejects match we're not gonna do D3 we're gonna do
07:49 the text we're gonna look at D3 here we're gonna look in there and see is there an email address we're gonna do comma and in quotes we're gonna put this regular expression and we have true now if this was like goog right now we're false if this was John John at gmail.com true this is looking at any text
08:16 it may have a plus sign it has any amount of characters here it has an at sign this is a literal at that we're looking for we're looking because all email addresses are going to have the at symbol we're going to have some text and then we're going to have a dot and then some other text that's what it's
08:36 looking for and this two or three is saying that is a two or three dot com dot co so look I think we're just going to know that it's not going to false but school still absolutely still true great so it gets any kind of email address there alright phone number validation does this contain a valid phone
09:01 number so this is like a set of three numbers then a hyphen then a set of three numbers then a set of four numbers and if you look here it's says d3 hyphen it is literal hyphen d3 hyphen d4 so this is essentially saying three digits hyphen three digits and four digits and we have true here lowercase 
09:23 detection so we just want to know is there a lowercase word this is great if you find yourself like checking titles and you want title case which is hello world like this if you're like oh i think we need to actually use like all omg right this is false but it's like oh my goodness that's gonna be true
09:49 it has some lowercase letters specific word if we are looking for an absolutely specific word we want just single thing the quick brown fox jumps over the lazy dog it's slash b fox slash b and it's true so if we change this fox if we want a different word let's say we want to know jumps it's also true
10:12 here but say it is goodness it is false goodness does not does not exist in this text we're always looking in the d for text or looking in for at this e column for the rejects and so you can change this to any word you want any right false there's not any.
10:35 The quick brown fox jumps over any lazy dog, true. See, now we are matching the exact word. This is probably really useful.
10:43 You're probably starting to think like, what is all the, I need to find that one particular word URL detection just saying does this have this like www dot or HTTPS slash blah blah.
10:56 Here's that example. Credit card validation if it's a valid credit card. In this case, it's quite complicated sort of set of digits that may or may not be in some certain range and some certain amount of numbers.
11:13 Same idea with IP addresses except there's a lot of dots in them. So like the some amount of numbers, then a dot, then some amount of numbers, then a dot, then some amount of numbers, then a dot, you get my drift here.
11:26 Social security number, very similar to the phone number one earlier, it's three digits, a hyphen, two digits, and then four digits.
11:36 If we're looking for hashtags, so let's say we've scraped, or downloaded a list of, say, Instagram posts, or Twitter posts, and we're like, hey, what are all of the hashtags used?
11:50 What are their hashtags used? Did these X number of tweets or Instagrams with X number of likes, did they get that because of some hashtag?
12:02 I had to use this a long time ago. I was trying to determine, one, if hash using hashtags had any effect on likes or on Instagram.
12:14 I also was compiling a database of hashtags from Instagram so I needed to know in this post does it have a hashtag or not.
12:22 And then I also used rejects extract to actually get the hashtags. So that's it. And it's the literal term hashtag, the little hash here, the pound sign, and then any number of letters.
12:36 And so here we have true, if we take out the hashtags here, I think even from this regular expression we have to take it out.
12:44 If we take out all those, it is now false. So there's no hashtags here. Zip code check. Is it just five digits?
12:51 That's all we're doing here. And these, this slash B. Is a word boundary. Meaning does it have some characters around it?
13:00 A non number, non letter characters? Or a space? So this text could be also a space. And it should be true.
13:13 If it is there, it is false, right? This is not a zip code. Even though it is five digits, it is not a zip code because a zip code is a single word with word boundaries.
13:23 And it's just true, right? And that's a slash B. No, so we start reading these. We can start reading these, right?
13:30 Four digits, two digits, two digits. Literally we're using hyphen there for rejects. Mention detection. This is just the same as hashtag except we're using an at symbol.
13:40 Very similar. Numeric. Value alphabetic, check, uppercase detection. Are there uppercase letters here? Are there any words? Sentence check. We're looking for sentences.
13:55 We want text and then a dot or some character that ends a sentence. Exclamation point. Question mark. We have the options in here.
14:06 And this is true. Acronym. Are we looking for a specific acronym? O-M-G in this case. Or L-O-L. It will probably be, oh, we have to change it over here, right?
14:20 L-O-L. It is false. Because this is not, if I, if it's not a word, because it has no word boundaries, so if I do L-O-L, it is true.
14:31 So let's change that back to O-M-G. Special character detection. Do we have characters that are not normal characters, right? Exclamation point, at symbol, hashtag, all of them.
14:43 We're looking for all of them here. New URL. Are we scraping like a bunch of text and we just want to know what are the YouTube video URLs?
14:55 This is really great if you're scraping YouTube descriptions and you're like, where do they link? What, where do YouTube video descriptions link to other YouTube?
15:04 Videos. I want to know that, right? I want to see all the connections here. This would be a great example of that.
15:09 ISBN connection has an emoji. This is really difficult. I had to use a lot of AI for this one because you couldn't use an exact emoji in the regular expression as far as I could try, but I figured out, not I figured out, AI figured out that it was this particular character, number, name and again, AI
15:31 is great, it's great for that. Twitter hashtag detection file. All path validation, are we, do we have C colon slash all that stuff, Mac address, IP address, do we have social media profile?
15:44 This one is specific for Facebook, just have facebook.com here Bing numbers, Roman numerals, you're scraping, this would be great if you're scraping like WordPress blogs and you're like, do they use Roman numerals to do they use Roman numerals for their bullets, right?
16:06 For table of contents or something, looking for either specific Roman numerals or any Roman numerals. Twitter URL, Instagram, these are all URL detections.
16:17 Here we have, this may look weird, right? This regular expression here says something like HTTPS and then has weird slash.
16:24 The reason is that a slash needs to be escaped. So instead of just two forward slashes, it has a backslash, forward slash, backslash, forward slash.
16:38 The reason for that is to escape that characteristic. It's usually a weird thing. So you'll see that pattern a lot more in these regular expressions.
16:48 All right, let's keep going through. We have some percentage range. We're looking for a particular percentage here, valid JSON. You want to look for a hex code or color code HTML color code.
17:01 This is really good if you're scraping blogs about colors and you're like, what colors do they or do they mention colors?
17:09 All that kind of good stuff. All right, again, you can get all of these 200 Regex examples down below. You're a BetterSheets member if you're watching this.
17:19 Get all of these examples in the sheet down below. You'll have all this also these y's and use Regex match or BetterSheets.co If you're looking for a very specific use case and you have the text that you want to match and you have the use case, you can describe it.
17:36 If you can describe it to me, you can describe it here and get it and it'll generate your Regex match for you.
17:42 There are Regex checkers again. There are a couple things that Google Sheets can't do, like look behind, look ahead. And I do want to warn you as well, sometimes it'll have a dollar sign at the end of your if Regex match generator or you use any other chat TPT AI to generate.
18:01 Rejects and you have this dollar sign. Watch out for that because it matches the end of the string or the end of the line but sometimes when you're describing what you want to match you may format it where it's only the thing you want to match and then it'll add this dollar sign and it'll be wrong.
18:18 Because you might be looking for it within some text. Same as go to the beginning and put this carrot this uppercase that means it matches the beginning.
18:28 So sometimes and I had to go through this a hundred times when I was trying to like get AI'd to generate my rejects of regular expressions.
18:38 This carrot shows up all the time because it's I only gave it one thing to match I didn't give it its context and so I had to go delete that and delete the dollar sign at the end if it gives it to you.
18:50 That's a that's a tip very quick tip for you if you're using AI to generate your regular expressions and you find that you don't work in Google Sheets you get a false is you get true when it actually is true or you get errors of like this is not a regular expression.
19:06 Like even actually this emoji worked well actually I can't believe this worked, but sometimes emojis didn't work, but they do fun, but yeah enjoy use all these examples.
19:19 I hope you start using rejects match if you do use.