Microsoft Excel Tutorial: Excel Adds RegEx Support for Pattern Matching in Excel.
Unlocking the Power of RegEx in Excel: New Features Explained! 
Hey Excel enthusiasts!
In today's video, we're diving into an exciting new feature: RegEx support in Excel for pattern matching. If you enjoy exploring new Excel functionalities, this is a must-watch! Don't forget to Like this video to help it reach more people. 
What's New?
Excel has introduced three powerful RegEx functions: RegExTest, RegExReplace, and RegExExtract. These functions are set to revolutionize how we handle data patterns. Plus, there are updates to XLOOKUP and XMATCH functions that now include RegEx capabilities! Let's explore how these tools can make your data tasks easier and more efficient.
Understanding RegEx Functions
RegExTest: Quickly determine if a pattern exists within your text.
RegExExtract: Extract specific patterns from your data.
RegExReplace: Replace patterns with desired characters or symbols. Perfect for anonymizing sensitive information like account numbers or patient IDs.
Practical Applications
We'll walk through real-world examples, like filtering data with digits, extracting specific formats, and masking sensitive data. These demos will help you see the immense potential RegEx brings to your Excel toolkit.
Bonus Tips & Tricks
Discover how to use these functions with the FILTER function and how to handle multi-part patterns. Plus, learn some insider tips on using RegEx efficiently in Excel. Whether you're new to RegEx or looking to refine your skills, this video has something for everyone.
Join the Conversation!
If you have questions or insights, drop them in the comments below. Let’s learn together! And don’t forget to visit MrX.CL/gpt to explore over 2,500 Excel tutorials and find answers to your specific questions.
Thanks for watching! Remember to Like, Subscribe, and Ring the Bell for more Excel tips from MrExcel. See you next time!
#Excel #RegEx #PatternMatching #ExcelTips #MrExcel #DataAnalysis #ExcelFunctions #Tutorial #LearnExcel
To download the workbook from today: Excel RegEx Support For Pattern Matching - Episode 2642 Sample Files - MrExcel Publishing
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
Table of Contents
(0:00) Intro to RegEx in Excel
(0:10) MrExcel GPT to answer your Excel questions
(0:26) What is RegEx?
(0:57) REGEXTEST function in Excel
(1:21) Using REGEXTEST inside of FILTER in Excel
(1:34) REGEXEXTRACT function in Excel
(2:28) REGEXREPLACE function in Excel
(2:50) REGEX in Excel lookup
(3:00) Excel RegEx returning multiple groups of data
(3:56) Extracting City and State for each Stuckey's Restaurant
(5:13) Hiding all but last 4 digits of Social Security Number
(6:02) Wrap-up
This video answers these common search terms:
excel regex tutorial
how to use regex in excel
regex functions in excel 2024
excel pattern matching with regex
new regex features in excel
excel regex examples
using regextest in excel
excel regexreplace function
extracting data with regex in excel
excel tips for pattern matching
Unlocking the Power of RegEx in Excel: New Features Explained! Hey Excel enthusiasts!
In today's video, we're diving into an exciting new feature: RegEx support in Excel for pattern matching. If you enjoy exploring new Excel functionalities, this is a must-watch! Don't forget to Like this video to help it reach more people. What's New? Excel has introduced three powerful RegEx functions: RegExTest, RegExReplace, and RegExExtract. These functions are set to revolutionize how we handle data patterns. Plus, there are updates to XLOOKUP and XMATCH functions that now include RegEx capabilities! Let's explore how these tools can make your data tasks easier and more efficient.
Understanding RegEx Functions RegExTest: Quickly determine if a pattern exists within your text.
RegExExtract: Extract specific patterns from your data.
RegExReplace: Replace patterns with desired characters or symbols. Perfect for anonymizing sensitive information like account numbers or patient IDs.
Practical Applications We'll walk through real-world examples, like filtering data with digits, extracting specific formats, and masking sensitive data. These demos will help you see the immense potential RegEx brings to your Excel toolkit.
Bonus Tips & Tricks Discover how to use these functions with the FILTER function and how to handle multi-part patterns. Plus, learn some insider tips on using RegEx efficiently in Excel. Whether you're new to RegEx or looking to refine your skills, this video has something for everyone.
Join the Conversation! If you have questions or insights, drop them in the comments below. Let’s learn together! And don’t forget to visit MrX.CL/gpt to explore over 2,500 Excel tutorials and find answers to your specific questions.
Thanks for watching! Remember to Like, Subscribe, and Ring the Bell for more Excel tips from MrExcel. See you next time!
#Excel #RegEx #PatternMatching #ExcelTips #MrExcel #DataAnalysis #ExcelFunctions #Tutorial #LearnExcel
To download the workbook from today: Excel RegEx Support For Pattern Matching - Episode 2642 Sample Files - MrExcel Publishing
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
Table of Contents
(0:00) Intro to RegEx in Excel
(0:10) MrExcel GPT to answer your Excel questions
(0:26) What is RegEx?
(0:57) REGEXTEST function in Excel
(1:21) Using REGEXTEST inside of FILTER in Excel
(1:34) REGEXEXTRACT function in Excel
(2:28) REGEXREPLACE function in Excel
(2:50) REGEX in Excel lookup
(3:00) Excel RegEx returning multiple groups of data
(3:56) Extracting City and State for each Stuckey's Restaurant
(5:13) Hiding all but last 4 digits of Social Security Number
(6:02) Wrap-up
This video answers these common search terms:
excel regex tutorial
how to use regex in excel
regex functions in excel 2024
excel pattern matching with regex
new regex features in excel
excel regex examples
using regextest in excel
excel regexreplace function
extracting data with regex in excel
excel tips for pattern matching
Transcript of the video:
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.
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.
