Excel RegEx Support For Pattern Matching - Episode 2642

Just down below the video, if you click Like.
That'll make sure that YouTube shows this video to more people.
Thanks. Well, this is super exciting.
RegEx support in Excel for pattern matching. There's over 2,500 videos here at MrExcel.
MrX.CL/gpt and 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 talked about it.
Every word that I've spoken on YouTube in over 15 years.
MrX.CL/gpt, give it a try. Alright, what is RegEx?
It stands for regular expressions. It's a fancy way of pattern matching.
I've seen it used in programming languages.
I personally have used it in Adobe InDesign where for whatever reason they called it GREP. I don’t know what the G and the P stand for.
But the “RE” is regular expressions.
It came to Excel insiders Beta, late May of 2024 with three new functions. Regextest, regexreplace, and regexextract.
And it's going to be rewriting the signatures for two existing functions, Xlookup and Xmatch.
Alright, so let's just take a look at these functions.
RegEx test, simply we pass it some text and a pattern.
In this case we're looking for any digit from zero to nine.
It tells us, yep, there are digits in that, but not in six bananas because six is spelled out.
Too many schnauzers, no digits. 2 Mini schnauzers?
Yes, there are digits.
Now what this is good for, well maybe we use it with the FILTER function.
In the FILTER function, we can say, Hey, give me all the items over here that have digits and that will work.
So we're passing the RegEx test as the second argument to filter.
Probably what's going to be much more common though is RegExExtract.
That's where we ask for a certain pattern. Now, check this out.
This is saying, Hey, backslash D is any number from zero to nine.
And then the asterisk says it is any number of digits.
Including zero digits, right? Very interesting.
If we wanted to specify one or more digits, we'd replace that asterisk with a plus.
So here it's getting the 12.
Because there's nothing here, we're getting nothing. 39 1 0 1.
Super interesting to me and it seems to be a bug, but it's probably just that I don't understand RegEx well enough.
Beverly Hills 90210 is returning nothing here with the D asterisk, but it's working with the D plus sign.
I don't get this, I don't understand why this one's not returning anything.
If you know down in the YouTube comments, let me know.
Right now I'm going to assume it's a bug or it's just a bug with me.
That's probably what it is. And then RegExReplace.
So that's where we match the pattern and then replace it with something.
So here we're looking for any digits from zero to nine and we're going to replace them with an asterisk.
This would be great if you had patient IDs or account numbers and you wanted to block that out.
It is not currently an XLOOKUP and XMATCH, but it'll be coming.
It's out in that argument that's called match mode.
Where currently 2 is “allow wildcard”.
They haven't said this yet, but I'm just going to guess probably three is going to allow Regex. Alright, a couple of really cool things here.
Here we have some sentences that talk about a monitor size.
I might want to extract both numbers there.
So the code is backslash d plus for any number of digits. And then an X.
Backslash d plus for any number of digits.
And then the parentheses say, okay, so we have some digits, we have an X and we have some digits.
But the parts that I want to return are the digits and the digits.
And so this is really cool the way that they implemented this. I'll come back here to that third argument.
Third argument is called return mode.
And I'm asking for capture the groups of the first match.
So in other words, I've defined two groups in the RegEx code and we want to return all of those groups.
Now, one frustrating thing is that what it did is it returned 1280 in this cell and 720 in that cell.
And since I wanted to go across, then I just send it into the transpose function.
But that one that I can see that is a fairly useful one.
Here's some data that I downloaded from the internet and I want to figure out where all of these Stuckey's locations are.
And so looking at the data, it looks like I want to go to the very first comma.
After the comma there's a space and then some number of characters.
We don't know how many characters followed by a comma.
Once I get to the comma, I know that I have a space.
And then a two digit state code.
Stuckey's is a chain just in the American Southeast. So I think seven or eight states.
I don't know the exact number, but I know it's always going to follow this pattern.
So over here we're starting out looking for a comma.
The period is a wildcard for any character. And then another period with plus.
So we don't know how long the city's going to be.
It might be long like Okeechobee, it might be Panama City Beach.
It might be three words like any number of characters up to the next comma.
And then there's going to be three characters, a space and a state. So the period followed by curly braces.
Three is saying the period is any character and the three.
And then what I specifically want to get is I want to get everything, not the space after the comma, but everything from there on out to the end of the state.
And it does a great job of getting that for me.
A few months ago I was doing a seminar and a wonderful person from the VA hospital was there and they said, we deal in last four social security.
So if we happen to download all of the data, I need to mask that out.
I need to show just asterisk and get the last four.
So here RegExReplace, the code we're looking for is three digits, a dash, two digits.
And we're going to replace that with asterisk dash, asterisk, asterisk, and it does a great job of that.
I think I probably should take another video and go through and talk about all of these great tokens you can use inside the text. They're very interesting how they work.
Microsoft in their blog post just gave us these simple little tips.
And pointed out that you could go to Bing Copilot and ask it for the RegEx patterns.
And that probably works as well. I think this is an exciting addition for Excel.
I know a lot of people have used Visual Basic where there was some RegEx support there and now we just won't have to do that anymore. So it's finally kind of bringing Excel up.
It is a very powerful language, but if you're not completely used to it, it is a bit intimidating.
I was always going to friends and saying, “Hey, how would I do this in Regex”?
But once you get down to the simple little tutorial.
It's not that bad of a new language to learn.
And there's certainly going to be cases where it's faster to use. Hey, I want to thank you for stopping by.
We'll see you next time for another Netcast 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.