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
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.😀
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.
@@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.
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?
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? ))
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.
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
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! :-)
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;-)
❗NOTE❗These functions are currently available on the Microsoft 365 beta channel, so you may not see them in your version of Excel yet.
Stoked about this function, but not available yet for us mortals it seems...
I am so glad to see REGEX in excel! Thank you for the video.
Great to hear 🙏
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 🙏😊
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.
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 😁
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!
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 😁
there is a competitor Excel channel i also watch who dropped a similar video but i much prefer Mynda's
Thanks for your support 😊
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: th-cam.com/video/1d3u3lmK1mQ/w-d-xo.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.
this is the formula that i sometimes use gg sheet then excel. goodjob MS
Glad you can do it all in Excel now 👍
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.
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! 😅
Microsoft have resisted real regex support for decades. Very nice to see useful regex support.
Indeed! 😊
I've been using this on google sheets for a while, but I still not getting the update on office.
It's still in beta testing. You'd need to be on the Office Insiders beta channel to get it. You can sign up for free to become an Office Insider.
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 😉).
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! )
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.
Is possible to use Regextract to extract date from a string of text ?
Yes 👍
Nice thanks
My pleasure 😊
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
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.
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!
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.
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 🤣
Finally!
😁
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. )))
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.
I won't have to import resources and write functions to get regex in Excel sheets? Oh, good news!
Yep 🥳
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?
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.