Microsoft Excel Tutorial: Using a RegEx match in Excel XLOOKUP
To download the workbook from today: Excel Adds Support For RegEx In XLOOKUP And XMATCH - Episode 2647 Sample Files - MrExcel Publishing
Welcome to another exciting update from MrExcel! In today’s video, we’re diving into a major breakthrough: RegEx support in XLOOKUP and XMATCH, which was just rolled out on August 14th, 2024. This new feature has been made available to 50% of insiders and promises to revolutionize how you work with data in Excel. If you enjoy the content, don’t forget to hit that Like button below to help more people discover this video!
We’ll start by exploring the new RegEx functionality in XLOOKUP and XMATCH. Imagine you have a dataset involving product deliveries to various cities with complex zip code ranges. Previously, this would have required convoluted formulas and multiple steps. Now, with RegEx, you can streamline your searches. I’ll walk you through a practical example where we use RegEx to match zip codes and return the correct order IDs—all in one fell swoop!
In the demo, I show you how to use RegEx patterns to identify zip codes for Los Angeles and San Diego within a larger dataset. By leveraging a RegEx pattern created with the help of Chat-GPT, we simplify the lookup process in XLOOKUP. You’ll see how changing the match mode argument to ‘3’ enables RegEx matching, allowing you to pull up data quickly and accurately. This new feature not only saves time but also ensures precision in your data analysis.
We’ll also test the case sensitivity of the RegEx and its effectiveness with zip-plus-four formats. Whether you’re searching for raspberries or kiwis, this update demonstrates how powerful and versatile the new RegEx support can be. The video includes practical examples and a step-by-step guide to implementing these changes in your own spreadsheets.
Finally, I’m curious to hear your thoughts on what other Excel functions could benefit from RegEx support. Drop your suggestions in the comments below—I’ll be forwarding them to the Excel team! If you enjoyed this tutorial, please Like, Subscribe, and Ring the Bell to stay updated with the latest from MrExcel. Thanks for watching, and see you next time for more Excel tips and tricks!
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel
Table of Contents
(0:00) Excel adds Regex support to XLOOKUP and XMATCH
(0:22) Figure out L.A. or San Diego from Zip Code
(0:57) You could do an extra XLOOKUP
(1:08) RegEx code for zip code ranges
(1:37) Putting RegEx as first argument in XLOOKUP
(2:05) Specifying Regex as the Match Mode in XLOOKUP
(2:54) Wrap up
(3:13) Please Like, Subscribe, Ring the Bell
#excelnew #excelformula #excelformulasandfunctions #xlookup #regex
This video answers these common search terms:
RegEx in XLOOKUP and XMATCH in Excel
XLOOKUP RegEx support tutorial
How to use RegEx in Excel XLOOKUP
XMATCH RegEx functionality explained
Excel RegEx match mode August 2024
Advanced Excel lookup techniques
XLOOKUP with regular expressions example
Using RegEx for zip code matching in Excel
New Excel features RegEx support
Excel XLOOKUP RegEx tips and tricks
To download the workbook from today: Excel Adds Support For RegEx In XLOOKUP And XMATCH - Episode 2647 Sample Files - MrExcel Publishing
Welcome to another exciting update from MrExcel! In today’s video, we’re diving into a major breakthrough: RegEx support in XLOOKUP and XMATCH, which was just rolled out on August 14th, 2024. This new feature has been made available to 50% of insiders and promises to revolutionize how you work with data in Excel. If you enjoy the content, don’t forget to hit that Like button below to help more people discover this video!
We’ll start by exploring the new RegEx functionality in XLOOKUP and XMATCH. Imagine you have a dataset involving product deliveries to various cities with complex zip code ranges. Previously, this would have required convoluted formulas and multiple steps. Now, with RegEx, you can streamline your searches. I’ll walk you through a practical example where we use RegEx to match zip codes and return the correct order IDs—all in one fell swoop!
In the demo, I show you how to use RegEx patterns to identify zip codes for Los Angeles and San Diego within a larger dataset. By leveraging a RegEx pattern created with the help of Chat-GPT, we simplify the lookup process in XLOOKUP. You’ll see how changing the match mode argument to ‘3’ enables RegEx matching, allowing you to pull up data quickly and accurately. This new feature not only saves time but also ensures precision in your data analysis.
We’ll also test the case sensitivity of the RegEx and its effectiveness with zip-plus-four formats. Whether you’re searching for raspberries or kiwis, this update demonstrates how powerful and versatile the new RegEx support can be. The video includes practical examples and a step-by-step guide to implementing these changes in your own spreadsheets.
Finally, I’m curious to hear your thoughts on what other Excel functions could benefit from RegEx support. Drop your suggestions in the comments below—I’ll be forwarding them to the Excel team! If you enjoyed this tutorial, please Like, Subscribe, and Ring the Bell to stay updated with the latest from MrExcel. Thanks for watching, and see you next time for more Excel tips and tricks!
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel
Table of Contents
(0:00) Excel adds Regex support to XLOOKUP and XMATCH
(0:22) Figure out L.A. or San Diego from Zip Code
(0:57) You could do an extra XLOOKUP
(1:08) RegEx code for zip code ranges
(1:37) Putting RegEx as first argument in XLOOKUP
(2:05) Specifying Regex as the Match Mode in XLOOKUP
(2:54) Wrap up
(3:13) Please Like, Subscribe, Ring the Bell
#excelnew #excelformula #excelformulasandfunctions #xlookup #regex
This video answers these common search terms:
RegEx in XLOOKUP and XMATCH in Excel
XLOOKUP RegEx support tutorial
How to use RegEx in Excel XLOOKUP
XMATCH RegEx functionality explained
Excel RegEx match mode August 2024
Advanced Excel lookup techniques
XLOOKUP with regular expressions example
Using RegEx for zip code matching in Excel
New Excel features RegEx support
Excel XLOOKUP RegEx tips and tricks
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. Quick one today.
Big news:, RegEx support in XLOOKUP and XMATCH came this week, August 14th, 2024.
About 50% of insiders fast.
In XLOOKUP and XMATCH, the match mode argument now has option three for RegEx. So here's a crazy set of data.
Where it looks like a produce company is delivering so many cases of something to city, state, zip.
And the what we need is we're looking up a particular product in a certain area. And we don't have the area.
All we have is a zip code. And how can we do this?
So L.A. is 900 + 2 digits or up through 918 + 2 digits.
But San Diego is 919 + 2 digits and then 92 with 3 digits.
So we're looking for all of those.
And I guess you could kind of do some crazy thing where you went to the zip code and added area over here.
But let's see if we can just do it all in one fell swoop.
So first Chat-GPT gave me the RegEx for everything that's 9 0 0 through 9 1 8.
And then 9 1 9 or starts with nine two. So got both of those from Chat-GPT.
I built the RegEx here basically where I'm adding in the product name and then doing an XLOOKUP to get the RegEx code. So those are the RegEx codes.
And then just putting that inside of the XLOOKUP. So building that RegEx right here.
If we took a look, it would give us the same answer we had on the previous page right there.
Beautiful. Look in column B.
And want to return the Order ID from column A. If it's not found, not found.
And you see that all of these currently are not found because we have to make the change.
So we come up here. Backspace.
And we're going to add a new match mode.
Instead of an exact match, which is the default, we're going to say it's a Regex match just by changing that to a 3 there.
It all works.
So raspberries to Los Angeles is order number 10017.
And let's see, where's apples? Apples to Los Angeles 10015. I made sure that pineapples is not a match.
It's a case sensitive Regex by the way. So actually I think we would've been okay there.
But the slash B here is saying that it's a word boundary.
And Chat GPT explains that pretty well.
And then the other test here is Kiwis, where it's a zip plus four and it's still managing to find order 10016.
Right? So there we go. Some crazy power.
We would've had to do that in several steps before.
But now thanks to this great new 50% of insiders fast having access to the RegEx match.
And the question is, what other functions would it be great for them to add RegEx support?
Let me know down on the YouTube comments what you think.
And I'll pass it on to the Excel team.
They said, “okay, these are the two we've done. What else? What else makes sense? “ There's probably a lot.
It would make sense. Hey, I want to thank you 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.
sure that YouTube shows this video to more people.
Thanks. Quick one today.
Big news:, RegEx support in XLOOKUP and XMATCH came this week, August 14th, 2024.
About 50% of insiders fast.
In XLOOKUP and XMATCH, the match mode argument now has option three for RegEx. So here's a crazy set of data.
Where it looks like a produce company is delivering so many cases of something to city, state, zip.
And the what we need is we're looking up a particular product in a certain area. And we don't have the area.
All we have is a zip code. And how can we do this?
So L.A. is 900 + 2 digits or up through 918 + 2 digits.
But San Diego is 919 + 2 digits and then 92 with 3 digits.
So we're looking for all of those.
And I guess you could kind of do some crazy thing where you went to the zip code and added area over here.
But let's see if we can just do it all in one fell swoop.
So first Chat-GPT gave me the RegEx for everything that's 9 0 0 through 9 1 8.
And then 9 1 9 or starts with nine two. So got both of those from Chat-GPT.
I built the RegEx here basically where I'm adding in the product name and then doing an XLOOKUP to get the RegEx code. So those are the RegEx codes.
And then just putting that inside of the XLOOKUP. So building that RegEx right here.
If we took a look, it would give us the same answer we had on the previous page right there.
Beautiful. Look in column B.
And want to return the Order ID from column A. If it's not found, not found.
And you see that all of these currently are not found because we have to make the change.
So we come up here. Backspace.
And we're going to add a new match mode.
Instead of an exact match, which is the default, we're going to say it's a Regex match just by changing that to a 3 there.
It all works.
So raspberries to Los Angeles is order number 10017.
And let's see, where's apples? Apples to Los Angeles 10015. I made sure that pineapples is not a match.
It's a case sensitive Regex by the way. So actually I think we would've been okay there.
But the slash B here is saying that it's a word boundary.
And Chat GPT explains that pretty well.
And then the other test here is Kiwis, where it's a zip plus four and it's still managing to find order 10016.
Right? So there we go. Some crazy power.
We would've had to do that in several steps before.
But now thanks to this great new 50% of insiders fast having access to the RegEx match.
And the question is, what other functions would it be great for them to add RegEx support?
Let me know down on the YouTube comments what you think.
And I'll pass it on to the Excel team.
They said, “okay, these are the two we've done. What else? What else makes sense? “ There's probably a lot.
It would make sense. Hey, I want to thank you 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.
