Excel Just Got NEW REGEX Functions You Need to Try!
HTML-код
- Опубликовано: 18 июн 2024
- REGEXTEST, REGEXEXTRACT & REGEXREPLACE finally bring RegEx to Excel!
👩🏫 Learn more with my Excel courses: bit.ly/regex24courses
⬇️ Step-by-step guide on how to use REGEX and a downloadable file: bit.ly/regex24file
Excel has 3 new functions that revolutionize text manipulation. And it’s about time because parsing text in Excel with functions like SEARCH, MID, and LEFT etc. can be cumbersome and frustrating.
These new functions allow you to work with regular expressions, commonly known as Regex.
❗NOTE❗These functions are currently available on the Microsoft 365 beta channel, so you may not see them in your version of Excel yet.
Regular expressions are powerful tools used for pattern matching within text strings. Allowing you to search, extract, and manipulate text based on specific patterns, making them incredibly useful for data validation, text parsing, and manipulation.
Reg-Ex uses a sequence of characters to define a search pattern. This pattern can match simple text sequences or more complex string structures.
Let’s dive into these new functions, with some practical examples to showcase their power and versatility.
LEARN MORE
===========
📰 EXCEL NEWSLETTER - join 450K+ subscribers here: www.myonlinetraininghub.com/e...
🎯 FOLLOW me on LinkedIn: / myndatreacy
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
⏲ TIMESTAMPS
==============
0:00 Functions that change the way we work with text
1:00 REGEXTEST
3:19 REGEXEXTRACT
4:44 REGEXREPLACE
5:43 Alternative you might be looking for
#Excel #ExcelFunctions #REGEX #REGEXEXTRACT #REGEXTEST #REGEXREPLACE #RegularExpressions 
Наука
❗NOTE❗These functions are currently available on the Microsoft 365 beta channel, so you may not see them in your version of Excel yet.
Great update. Updating people about new features and function really helps, because softwares get updated usually without you knowing the tools and function newly added. Thanks for the videos
Glad it’s appreciated 🙏😊
I am so glad to see REGEX in excel! Thank you for the video.
Great to hear 🙏
Thanks Mynda.⭐I can certainly see a big use case in data cleanup and data extraction of large datasets.
Awesome to hear 👍
Looks great Mynda - probably not much use to me but good to know when/if the time comes! Thanks - I love learning new things even if no use to me!
Great to hear! Never stop learning 😁
Saw these on Leila's channel too, can't wait to get these Mynda. They look really useful!
Cheers, Chris! Have fun with them 😊
This is fantastic, thanks. Now just need it in Power Query!
One step at a time 😁
I don’t need/use the full power/capabilities of Excel at work, we just use as a basic base.
But I do play around with Excel at home. I can see me using this once it’s available. I’ve only ever heard of REGEX but never actually seen it used, I’m looking forward to learning this.😀
Awesome to hear you'll be able to make use of them!
Nice thanks
My pleasure 😊
It's about time that Excel added this. Thanks for another crystal clear explanation.
Glad it was helpful!
Indeed, OpenOffice and LibreOffice have regex as long as I remember.
this is the formula that i sometimes use gg sheet then excel. goodjob MS
Glad you can do it all in Excel now 👍
Finally!
😁
I believe, the identifier part (before the @) of an email address may also include the underscore (_) and tilde (~) characters so I guess they also need to be inserted in the regex, shouldn't they?
Possibly, Paul. I'm not a RegEx expert (yet 😉).
Can you place formulas WITHIN the regex pattern? Also, can the regex pattern handle references to arrays?
Yes, as long as they return a text string. I haven’t tested whether the pattern can handle arrays but I doubt it because what would it do with the arrays…there’s only one input of text. 🤔
For array references, you'll need to fire up VBA. First, go to tools> references and make a reference to Microsoft VBScript Regular Expression 5.5. Then, just iterate over the range OR, write your regex to handle all the text at once, which would require you to read the whole array into a string.
Not got it yet 😪 but it should be useful and could hopefully save using PQ on occasion. Much as I am a PQ fan, and Pivot Tables for that matter, I find the need to refresh more and more annoying.
I know what you mean. This video covers a few ways you can auto-refresh PivotTables: ruclips.net/video/1d3u3lmK1mQ/видео.html
@@MyOnlineTrainingHub Thank you and it is useful from time to time, but nothing beats the instant "refresh" of formulas.
I hoped I could do away with many pivot tables when pivotby & groupby came along, but the need to manipulate data first means that PQ & Pivot tables generally provide an easier & faster overall solution but with the need to refresh. I wonder if Regex can change that, although the syntax is a real problem for me at first sight; AI should help though. Or maybe someone will come along with a Regex GUI add-on.
Your videos are great! Thank you.
Is possible to use Regextract to extract date from a string of text ?
Yes 👍
Any option that array functions could work inside the table as they throw #spill error
No. There are no plans to support spilled arrays inside Tables.
Only been waiting 30 years for this! Will Excel REGEX support positive/negative look behind/ahead?
Great to hear, Ian. Yes, it supports positive/negative look behind/ahead. They use the PCRE2 library.
Regex? Really? This is cool! ))) So, does it support non-backtracking, balancing groups, groups naming, options for modifying behavior on the way of the text? ))
Not sure as I'm not a RegEx expert, but I can tell you is uses the PCRE2 library if that helps.
@@MyOnlineTrainingHub Yeah, I looked thru function description where it says that. Thanks! )
there is a competitor Excel channel i also watch who dropped a similar video but i much prefer Mynda's
Thanks for your support 😊
Got so excited only to realise that I am not one of the chosen few!
I know how you feel. It took me two PCs to find one with it, which is a lot less than the 7 it took to get Python! 😅
I am using MS 365 - Beta Channel - Version 2406 -(Build 17716.20002) but this function is not available. Why?
They only roll it out initially to 50% of devices. You can try another PC if you have one. It took me two goes this time but for python it took me 7!
this function is not available on my office. I hope this function can be updated in the office soon.
Yes, it's just been released in the beta version, so it'll be months before it's generally available. You can get the beta version for free by joining the Microsoft Insiders program: insider.microsoft365.com/en-us/join/windows
Microsoft have resisted real regex support for decades. Very nice to see useful regex support.
Indeed! 😊
Sadly, they don't appear in functions list... 😪 (Version 2406 Build 16.0.17716.20002). EDIT: On another computer they DO appear!
It took me two PCs to get them too 😅
@@MyOnlineTrainingHub 🤣
I won't have to import resources and write functions to get regex in Excel sheets? Oh, good news!
Yep 🥳
You should tell, this function is still in Beta and not available for everyone yet at the time you made the video.
I put that note in the pinned comment because eventually they won't be in beta, and this video will live on long after that.
30 years to late and the syntax makes absolutely no sense. A RE is an RE and is very well defined outside this "new"
MS context. I'll stick with Sheets which follows well established standard;-)
The new RegEx functions use the PCRE2 library.
Amazing how people that call themselves data experts have never heard of RE...Where have they been since the late 1970's...?
Excel became a data wrangling tool in the last couple of years. Until then, it was a limited modeling tool for business.
@montebont, most data experts these days were probably not born until the 90s or later! 😆
@@MyOnlineTrainingHub which means that they should know about RE's that have been around - and doing a perfect job - for at least 20 years...
@@MyOnlineTrainingHub It's R&K stuff. If you don't now what that means...you're don't know the first thing about data processing...
I'm not sure where your original comment is aimed. Who said they never heard of RE?
WOW! This is so new, I’m still downloading the update…
But, still, WOW! Very powerful, indeed.
OK, caveat: now I need to learn about REGEX.
Thanks for this Breaking News, Mynda! :-)
😁 so pleased you're excited about these new functions, Geert!
You MUST learn it! It's cool and neat. )))
Very interesting. Kinda intimidating, too. 🫢
Thanks for the video. I'm trying to get myself up to speed on these new functions.
I know what you mean, Oz 😉 thanks for watching.