Excel - RegEx Building Blocks - 2643

Just down below the video, if you click Like,  that will make sure that YouTube shows this   video to more people.
Thanks.  The building blocks for Learning RegEx and Excel.
There's over 2,500 videos here at MrExcel.  Mrx.cl/gpt come here, type your question.
“Repeat a pivot table for each customer.”  It's going to give you a  short summary of the videos.  And then it's going to give you links to  the videos where I've talked about it.  Every word that I've spoken  on YouTube in over 15 years.  MrX.CL slash gpt, give it a try.
After watching this video, you will have all the   building blocks you need to put together RegEx.
Make sure to download this tip card, two pages,   with a summary of everything we  are going to talk about today.  Okay, in the previous video you talked about  these three new functions that came to Excel,   RegExTest, RegExExtract, RegExreplace.
And I had mentioned that I had a RegEx 101,   but that I'd do it in another video.
So this is that video.  This hopefully will just get you up to speed.
I have to tell you, in all the years up   until the point that it was in  Excel, I was horrible at RegEx.  I would always just go ask a friend how to do it.
But after studying this,   it's really not that hard.
It's pretty simple to understand.  Okay, so let's just start off,  put some letters in the pattern.  D, E, F.
That's saying that I want the   letters D, E, F in sequence.
Alright?  But there might be something where we  want either the letter D or E or F.  And so that's in square brackets.
This is a letter group and that   will match if there's a, D, E, or F.
But sometimes you don't want to have   to type the whole thing.
D to H will give us one   letter from the letters D through H.
So here's a whole bunch of different examples.  We're looking for D, E, F, and the  only one that has those three letters   exactly is A, B, C, D, E, F.
None of the others match.  But if we ask for square bracket, D, E, F, then  that's anything that has a D anywhere in it.  This one, this one, this all match or a  wider variety D through H picks up the G,   G, G and the H, H, H.
So square brackets means one   of the items in this group and you're  allowed to have a dash in the middle.  Alright, next up, what if you want to  start with a particular bit of text?  So the cell has to start  with the put a caret before.  Caret, t, h, e, we'll start with “the”.
Or if it has to end with something,   end dollar sign will end with end.
Right now, just right up front we   have to talk about this super annoying thing  that the caret, it has two different meanings.  This has happened before in Excel, like the F4  key puts dollar signs in if you're in edit mode.  Or repeats the last command If you're  not in edit mode, it has two jobs.  Well, here in RegEx, the caret inside  of a letter group means not X, Y, or Z.  So in this case, this caret is saying it has  to start with something that's not A through M.  A little confusing that the caret has two  different meanings, but you'll get used to it.  So if we're looking for something that  starts with the, there's only one.  If we're looking at something that ends  with end, it's this one or that one.  If we're looking for something that  contains a character that's not X, y, or Z.  Alright?
So this one does not contain a character   that's not X, Y, Z, but everything else just  about contains characters that aren't X, y, Z.  This one is actually far wider.
It means that it contains a character   that's A through Z capital, A through W,  lowercase, any of the digits, zero to nine,   any of the special characters.
Yeah.  And then this is one that starts with  something that's not A through M.  So a lot of these start with  something that's not A through M.  Even the I, that's a capital I.
And we're asking for lowercase A through M.  Alright, so that starts with and ends with, right?
This is all pretty easy so far, right?  Except for the wild cards.
Wildcard.  A wildcard: a period is a wildcard  for any single character, anything.  So what if you need to look for an actual period?
Then you put a backslash period.  So the backslash is kind of  a way to escape, to say, hey,   RegEx is using this period to mean something else.
A wild card in this case, put a backslash before   to say no, we actually want a real period.
There's a lot of special characters, okay?  So in this case, zero to nine is looking for  any single digit, but there's a beautiful   shortcut for that.
Backslash D is also   the shortcut for any single digit.
All right, now let's talk about how to repeat.  In this case, the period is  looking for any character.  And then the three is saying we  have three of that character.  Or we want at least two, but up to three.
So put a comma in between them.  So we're looking for a specific repetition.
Alright?  So here any character that  period means any character.  All of these are going to match except for  the blank cell, there's no character there.  But if we put a backslash period, well then only  the thing that has the period is going to match.  Looking for anything with a digit anywhere in it.
So we get those two and all of these have digits,   but it's shorter.
Just put backslash D,   it'll be the exact same answer here.
These are identical.  Identical column F and G.
Alright?  This is something that has  at least three characters.  So A has only one character  doesn't match this one.  The n has eight characters total,  including the space and the period,   but as long as it's more than three, we get it.
These two and these two only have two characters.  So they don't match here, but they do match here  where we're allowing two or three characters.  Alright?
So that's how you specify that we're   looking for a certain number of things.
Alright?  So remember this wild card is for any single  character or back slash D is for any single digit.  We're going to repeat those here under wild card.
So we start out with those two.  Period is a wild card for any single character.
Backslash D is any digit.  This is a great way if you want lowercase A  to Z or uppercase A to Z, it looks like this.  And then they have something for digits,  lowercase A to Z, uppercase A to Z, and that's W,   lowercase W is the code for any of those.
It seems like that'd be really useful.  We use that one a lot.
Back slash capital W means the reverse of that.  It's not A to Z or lowercase  A to Z or zero to nine.  This happens a lot where you take the lowercase  and you capitalize it and it makes it into a Not.  Okay?
Now we're going to talk about spaces here.  Actually two tabs from now backslash s  is a space or any kind of a white space.  There's all kinds of spaces that you  might encounter, like a character   160 space and that'll be handled.
And then not a space, not white space.  We'll look for something that's a non-space.
So here, if we're looking for a digit,   that's a match.
If we're looking for   something that's a letter while the one is not  a match, the exclamation point, not a match.  And then the only thing here that doesn't  fall into the WW is a great catchall,   A to Z, uppercase, lowercase, and digits.
Special characters like the exclamation   point or anything shifted along the  top row of your keyboard will be that.  And then not a W, right?
So W is A to Z, upper or lower zero to nine.  Well, what contains not a W?
Well the exclamation point, certainly it does.  But also here there's a space.
So a space Z or A space capital Z,   those fall into the not a W.
Okay, so now check out these two.  This one is saying that we have an A  followed by a space followed by a Z.  So right there, that one matches  because it's K sensitive, it's   not going to see the capital A, space Z.
And in this case it's with the capitals   slash capitals says something that's not a space.
In other words, we start with an A end with a Z.  And in the middle we have a letter  so that A and BZ there will work.  Next up, repetition.
Alright, so we know   back slash D is any digits from zero to  nine, a single digit from zero to nine.  What if we need to look for multiple  digits, like 1 23 main or 1234 Peyton place?  Alright, so the first thing they have,  and I hate this one back D asterisk   says there's going to be some number of digits.
We don't know how many digits.  There might be two digits,  there might be seven digits.  But it's also possible with  the asterisk that there might   be zero digits, which is really weird to me.
Why are we saying, hey go, we need a digit here,   but in fact there might be no digits at all.
So back slash D is getting everything here   that has a digit, that's great, but back slash d  asterisk is also returning things that have zero   digits at all.
Right?  Now in my RegEx journey, this  one seems pretty useless.  I'm sure there'll be use cases later, but what we  really need I think is D plus that says there's a   digit at least one and maybe more digits in a row.
So like a one, two, a, 1, 2, 3, 1, 2, 3,   4, 1, 2, 3, 4, 5, A, one Z.
But when there's no digits and then it's   not going to show up as true for that.
Alright?  The other one that's interesting is there can  be a digit or it's okay to not have a digit,   but we can't have two, three, or four digits.
So D question mark is specifically zero   or one digit.
They say that   the question mark means that it's optional.
There could or could not be a digit there.  So the way that I built this is we  need a letter from A to Z, lowercase,   and then backslash the question mark.
And then another letter from A to Z select.  Looking down here, this one fits the pattern.
A one Z is lowercase letter A, one and a Z.  And then even here, lowercase letter,  no digit and another lowercase letter.  Those both work.
But here where we   have a and then three digits in a row  and then the Z, that one does not work.  See, and it's also funny, all of these are  marked as correct because there's no digit.  So we have two lowercase letters and no digit.
Alright?  If we know specifically that we need three  digits slash D and then curly braces three,   or you're allowed to put a range in here,  like anywhere from two to four digits.  So curly braces two comma four is  between two and four digits like that.  So here slash D three, it's only the  ones that have three digits in a row,   but also four digits and five digits.
These have 1, 2, 3, 1, 2, 3.  It's definitely there.
So it finds the pattern.  Even a 9 9, 9 Z with three digits will  fire there between two and four digits.  That picks up a1 two.
That wasn't true before.  And again, all the others, even things with five  digits because it's saying, Hey, is there between   two and four digits?
Yes.  And then after that there happens to be a five.
All right, this one is interesting to me.  So if we have something like sounds like bur  it's cold, that could be spelled with three Rs,   four Rs, or five Rs, but not BR,  and not even BRR in my rule set.  So we're looking for B and then anywhere from  three to five Rs followed by an optional space.  So with that pattern, the BR and BRR are not true,  but the ber or ber burr is coming up as true.  Now the thing that's interesting to me is  that three to five refers to just the one   character before it.
What if we wanted to   have the word repeat the entire word?
So then we're going to put the whole thing in.  So now we need BRR space anywhere from two to  three times, and that will be true in that case.  So the parentheses creating a group  that the repetition direction will   refer to that entire group.
I was super interested in how   they're going to deal with white space.
Alright, so if you're just looking for   a space backs slash D is any  digit, the space back slash D.  So we're looking for digit space digit.
Now, one hassle with this is, so that matches   nine space one, which is what I was thinking of.
But it also matches nine, nine space,   nine nine because in the middle of that  whole thing is a digit, a space and a digit.  So if we want to say it has to start with the  digit, then a space and then end with the digit,   you need that caret at the beginning that  says it starts with dollar sign at the end   says it ends with, and that'll make sure that  that one matches, but this one does not match.  Okay?
And then other   kinds of whitespace back slash t is a tab slash  N is what you get when you press al to enter.  That's also known as a character.
10 VB is a VBF.  Some people call it a line feed  back slash R is a carriage return,   that's a character 13 or a V, BCR and VBA.
And then back slash s is either a space or   any of these three get counted as white space.
Basically think of it as some sort of white space.  And then backslash, not S is saying  that it would not be a space, right?  So in my codes across the top here,  this is backslash backs slash s,   and this has an axial space there.
And I also found when I was looking to   see how character one 60 would be handled  that backslash H in some implementations   of RegEx will give you more spaces.
So here I have word alt enter wrap,   and it gets caught by the backslash N,  but it also gets caught by the backslash   S because remember backslash S is looking for  tabs, line feed, carriage return or spaces.  Okay?
So non breaking spaces, this is one   that just never gets handled well by Excel.
And if we're pulling data off a website,   we're just scraping data off a website, there's  going to be character one 60 spaces there.  So right here I have evilly used the formula  to put in a number one and then three   character, one sixties, and then the number nine.
So does that get seen as a digit space digit?  No.
Does it get seen as starting with a digit,   a space and ending with a digit?
No.  Does it get seen as any of these?
No.  But it does thankfully get  seen as slash s and slash h.  Now out in Stack Overflow, they actually said  that you'd have to use back slash H for this.  And it seems like Excel is being better with this.
They're understanding that when we say   back slash s, they're treating the character  one 60 as a space, it sees it as a space.  It doesn't see it when we just type  a space, but if you use backslash S,   you're going to get all of those spaces.
Alright, so here's the problem.  So in this one, and then character 1 63 times  nine when I try and use trim, trim in Excel   will get rid of the interior repeated spaces.
So that should change it to one Space Nine,   but it doesn't see the character one 60  as a space, so it doesn't do anything.  So down here I'm going to use RegEx to  replace, let's put that formula next to it.  So I'm going to replace the  backslash S with a real space.  So that converts all those evil character  one 60 spaces to regular spaces right there.  That's worth the price of admission.
That formula.  We should make that formula red  because that's a formula I'm   going to come back to again and again and again.
And then once I've converted the evil character,   one 60 spaces, the regular spaces,  then Trim does the right thing.  It's funny, the character one 60 is the  one that we run into all the time because   the web is popular, but there's all kinds of  weird spaces out there and it very nicely,   a space character only matches a character 32.
But in Excel anyway, slash s slash h are both   programmed to find all of these weird spaces,  M spaces and spaces, all kinds of weird things.  We never run into any of these  except for character one 60.  But maybe if you're, I dunno, I don't  know why you would get any of these,   but in case you do great news, they're handled.
Alright, now look, there's one last thing here   and this is really, really powerful.
Alright, this is matching and then   selecting a group from the selection.
So here we're looking for any character   plus means any number of any character  followed by specifically a period.  And then P, D, F and ends there, right?
There's nothing after P, D, F.  So this will get all of these files that are  P, D, F files, but not the Excel files, right?  Good enough.
Let's modify that.  Now that I know that it's a pdf d,  I don't need to see the PDF anymore.  So I modify that pattern to put a parentheses  there and that says the only part that I want is   all of the characters up to the period.
I don't want the period PD,   F get me everything before that.
So check out that pattern with the group.  That's called a group.
I'm selecting a group.  It will return everything up to the PDF.
Now, we had to modify this formula,   the regex extract.
There is an argument   out here called return mode, and this is  called a capture group of the first match.  So we have to change that argument that  really was just defaulting to zero to be   a two to make that work.
It's pretty cool.  So then here I want to find  everything that's an Excel file.  So either X-L-S-X-X-L-S-M, XLSB, I'm ignoring  the templates for right now and notice that I'm   allowing either XMB and the question mark.
That question mark is saying, Hey,   there could be zero of those or one of those.
So let me capture XLS and it has to end with that.  So all of these get us the file name here.
Oh, except for this one.  What's up with that?
How?  I don't know how I got the file extension to  be capitals scatter, other graph template.  That must be ancient.
I have no idea how that's there,   but it's there so it's not being seen.
So the modification here is to come   out and do a case insensitive match.
So the case sensitivity allows me to handle that.  That's an important part.
And then finally, all right,   check out this awesome little bit of code.
I took this one and I said I want the file name.  So that's the parentheses.
And then, hey, since I don't   know what the file type is, go ahead.
I don't want the dot, but I want   everything else after that.
So give me all of the stuff   that's either XLS or x Ls X or x ls M or  XLS, B and return that as a second group.  All.
Now for this one to work or this   don't want to work, and I mentioned this at  the end of yesterday's video, I had an example.  We're saying that we want the two.
So we're getting all of the groups.  And when I do this, it's going to put the  first thing here, the file name in this cell,   and then it's going to put  the extension underneath it.  It's coming back as a vertical array.
So I just have to transpose that to make   it be a horizontal array.
All right?  But there I'm defining multiple  groups and I'm getting them all.  All right, so there you go.
There's the RegEx 1 0 1,   I think with this workbook.
And by the way, you can download this workbook.  There's enough examples here to get us through  a lot of the types of pattern matching that we   might need to do.
So there you go.  Hey, thanks for stopping by.
We'll see you next time for another   net cast from MrExcel.
If you like  These videos, please down below,  Like, Subscribe, and Ring the Bell.  Feel free to post any questions or  comments down in the comments below.