Google Sheets - RegEx REGEXEXTRACT, Functions Exctract, Replace, Match Tutorial - Part 1
Get 300 checks per monthabsolutely FREE!
No credit card needed. No strings attached.
Today I'm going to cover a couple of amazing functions that are available in...
today I'm going to cover a couple of amazing functions that are available in Google sheets and they're related to regular expressions so if you're familiar with the regular expressions then it's going to be very easy for you to understand if you're not just the warning this is more on the advanced side of using Google sheets so for a beginner it might not be very easy to understand a lot of times but the functions we'll be covering are called rag X match rag ex extract and rag X replace and mostly we'll be concentrating on reg ex extract and reg X replace functions but again if you can figure out how to use one of this then it will be possible to figure out how you can use all the others so and a lot of of what I'm really going to be covering today is really like basics of understanding regular expressions and
How you can use them and just to emphasize it is not going to be in...
how you can use them and just to emphasize it is not going to be in detailed coverage of regular expressions so if you really want to dig deeper into it you may want to just look into regular expressions on their own right so now what do we get out of this functions that I'm talking about right so this functions that we have so if you have experience with text functions in Excel like for example there is substitute function where you can search and replace something in a text there's also like split function where you can split text by a character so these are like those on a steroid so if you want to think about like functions that will cover a lot of those functionalities and then some more this is it so let's see what we're actually talking about right so first of all the regular expression so I'm going to start with our reg extract function so I'm going to go
Equals reg extract and the first argument is the text we're working with...
equals reg extract and the first argument is the text we're working with so what is the original text so in this case I'm going to take this email address that we and I'm going to do it , and the second argument is going to be the so called regular expression and it has to be in as a string that means in quotes from both sides so I'm going to go coat and what I'm going to do I'll just try something simple just to start with so I'm going to do edu and closed parenthesis and right here code M parenthesis and it enter and you can see that it says edu so what does that mean right so what I'm going to copy this down so you can see what's happening here so every text that has the word edu in it you can you see we're extracting that text out of it and we're getting that text as a result which is definitely not very helpful in this particular case but
That's a start so you can think about this that it's...
that's a start so you can think about this that it's checking if this text contains that edu and if it contains it kind of returns it that's pretty much what it's going going on here now but that's not really helpful right so let's get to regular expressions when they actually get become a lot more helpful than this so let's say what we want to do we want to grab everything in this text starting from this add sign in the email and going forward so I'm going to type @ sign because that's going to be the beginning character I want to extract which is the ED sign that's where I want to start and then I want all the characters after so the way you tell all the characters so first of all there is a the there is a dot that I'm going to do now the dot in regular expression has a special meaning it's not looking at
That dot as just a character the which is like not what is looking for it means...
that dot as just a character the which is like not what is looking for it means any character so right now if you look here in the blue it kind of tells us what the result is going to be so what it's doing it's taking the @ sign and the next character after it whatever character it may be it's going to extract it so right now if I copy this down you'll see that is going to take the ED signs and the next character after it and that's exactly what it's doing so dot meaning any character now what's interesting about this is that we want all the characters after right so the way we can do that we can do plus sign immediately after the dot and what plus means it means one or more of the character that we have before the plus now the character we have before the plus is this dot which means any character so we're saying that after the @ sign we're going to have one or more of any character and in this case is basically
Going to mean all the remaining characters after the ad so now if I go...
going to mean all the remaining characters after the ad so now if I go ahead and hit enter copy this down see we have our results just like that so we extract it from the add sign and going forward everything that follows that now to make this easy because this takes a lot of time so every time you know type the new regular expression here and then try to copy the formula down to see what happens what I'm going to do I'm going to take this regular expression we have and type whatever that regular expression is going to be right here so that's going to be our you know they are at dot plus right that's what we're using and here I'm going to say equals two so and again I'm going to hit escape because I don't want to type the whole thing in so we're saying D two and instead of here typing the actual regular expression I'll just point to the cell that contains that regular
Expression on top and because I would like to copy this down so we can see...
expression on top and because I would like to copy this down so we can see what results were getting right away I'm going to lock this cell on top so that's our F four to add a dollar sign for the column and dollar sign for the row that's going to lock the cell on top and because it's locked we'll be able to come here and I'm going to copy this text down are there are more records here that I thought but there it is so now the advantage of doing this is that I don't have to get inside of this formula every time to get the updates Oh I have to do is just go here on top and update my regular expression so if I switch this back before the Plus hit enter see we're getting the results we were having before so so far we know that a dot means any character so what that means is that if we wanted to extract everything that starts with the letter I don't know T and then any
Character after that and then L as a next character right so that's gonna be...
character after that and then L as a next character right so that's gonna be easy apparently this one is tell in it now I'm not sure there are any others so see this one is T this at sign and L so we've extracted that and everything else doesn't have anything so we're getting na as a result for this so that again any character is the dot now the plus after any character means any number of this particular character we have now what if I wanted to get the extension of each domain right I want to get this edu Co and the CA and edu and org and you know Co UK and so on we're trying to get the extension of the domain so how can we actually accomplish that so the way I can accomplish that is first of all I
Have to figure out where the pattern is and try to match that pattern well we're...
have to figure out where the pattern is and try to match that pattern well we're going to have dot some characters and sometimes it could be three sometimes it's something else right then dot then two more characters now in this case now in the second case it's just a dot e-d-u that part right so we're trying to figure out how to extract it now one interesting special character in a regular expression is the dollar sign so and what a dollar sign is is basically it means the end of the text we're expecting that to be the end of our text so if and before I guess I get into it I'll have to cover a few other things so we know what that what exactly we're doing here so the first thing I'm going to cover is the /w again /w has a special meaning and it means
Alphanumeric characters so it includes all the alphabet letters and it's going...
alphanumeric characters so it includes all the alphabet letters and it's going to include all the numbers and it's going to include underscore which is kind of a weird definition of a word character but that's kind of how it works now this I'm saying just get one word character and it's getting the first one because we said one of them now if I do the plus sign that will mean one or more of that now what's going to happen is going to keep going until it hits a character that's not that often numeric or underscore and at that point it's going to stop so it extracts from beginning to that dot which is not often America anymore right so that's what we're extracting here now let's say we also want to extract the dot after that that we have right we want to match the
Ones that have the the first word and then dot and then we want to grab the...
ones that have the the first word and then dot and then we want to grab the second word so that's what we're trying to match so what I want to say is after this that we've matched we have to have this dot character so if you remember if I do dot dot in regular expression has a special meaning meaning any character so the way we can actually say that what we're saying here is actually just a dot it's not that special regular character character is by using this escape character which is this I guess for verse slash we're calling this so that's going to be our escape character so we were placing that before a special character in regular expression that means that we actually mean that character so right now if I hit enter see you the word and the dot after the word that we have so we go word and the dot after it so let's see if we have any words
That don't have a dot in them so this one and you can see what happened it's...
that don't have a dot in them so this one and you can see what happened it's not matching the beginning here it it does this part doesn't match that pattern what does match that pattern is the second part where it's dis and then dot and ca and that the pattern is matching as a result of that now but let's just kind of learn the basics now let's say after the dot I'm expecting some other word characters and I'm expecting one or more of those characters so that's what it's going to do so it's going to find something then dot then something else after that so here it wasn't able to find it in the beginning but it was able to find a pattern similar to that in the end right now another thing I said is that the dollar has a special meaning so if I put dollar in the end that means it ends with this so it's going to try to find something that has some words and
Then the dot after that and then some other words and then that's the end of...
then the dot after that and then some other words and then that's the end of it so here see the way it was able to find it is where this edu that's the word one or more of text character and then we have this dot after it and then see you that's what it was able to match and so on now that's that's looking for that to be in the end of the text which is this is our end of the text we have and then we have the end of the text here the end of the text here the end of the text here and so on right so that's pretty good I guess but again we could have a little problem here because in some cases here like the domains we have there edu oh right that's what we have that's our main and then this is just et you so if
You were trying to just extract the whole domain name for example and we...
you were trying to just extract the whole domain name for example and we kind of did it with that at sign but we didn't exactly do it we'll kind of get back to that example but that's not really extracting just a domain name what is extracting is that well in this case it's actually matching that it's the domain name ends up being that pattern but in this case well not the domain name I'm sorry I meant the extension here we're getting the extension but here it's not the extension is actually the whole domain name because it's not one of the domains that has to you know dots in the extension so then we're going to have to find a different way to achieve what we want now to get to it we're going to have to learn some other special characters so what so far we know that /w is often numeric or underscore character we know that if we want to just do dot that will be this escape dot because the regular dot means any character we know that plus means one or
More of the character that we specified before that and we also know that dollar...
more of the character that we specified before that and we also know that dollar sign means that's the end now I'm going to go back for a second to our example that I did when I did like the add sign and then I did one or more word character or actually I did any character after that one or more so that was extracting the add sign and the domain name but let's say what I really wanted here was to just get the domain name I don't want the add sign right so I can't accomplish that by using this grouping in regular expression and the way you can group something is by wrapping it in parenthesis from the beginning and the end so I didn't want the add sign and I'm grouping the part that I actually want to capture which is whatever's after the @ sign so I'm going to put that in parenthesis hit enter and you can see now now we don't have that at anymore as a result we're still
Looking for that pattern that starts with ad but we're extracting only what's...
looking for that pattern that starts with ad but we're extracting only what's in our parentheses
Get 300 checks per month absolutely FREE!
No credit card needed. No strings attached. 👍