❗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

@MyOnlineTrainingHub

Ай бұрын

Glad it’s appreciated 🙏😊

I am so glad to see REGEX in excel! Thank you for the video.

@MyOnlineTrainingHub

2 ай бұрын

Great 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!

@MyOnlineTrainingHub

Ай бұрын

Great to hear! Never stop learning 😁

Saw these on Leila's channel too, can't wait to get these Mynda. They look really useful!

@MyOnlineTrainingHub

2 ай бұрын

Cheers, Chris! Have fun with them 😊

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.😀

@MyOnlineTrainingHub

2 ай бұрын

Awesome to hear you'll be able to make use of them!

This is fantastic, thanks. Now just need it in Power Query!

@MyOnlineTrainingHub

2 ай бұрын

One step at a time 😁

It's about time that Excel added this. Thanks for another crystal clear explanation.

@MyOnlineTrainingHub

2 ай бұрын

Glad it was helpful!

@xbia1

2 ай бұрын

Indeed, OpenOffice and LibreOffice have regex as long as I remember.

Nice thanks

@MyOnlineTrainingHub

2 ай бұрын

My pleasure 😊

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?

@MyOnlineTrainingHub

Ай бұрын

Possibly, Paul. I'm not a RegEx expert (yet 😉).

this is the formula that i sometimes use gg sheet then excel. goodjob MS

@MyOnlineTrainingHub

2 ай бұрын

Glad you can do it all in Excel now 👍

Can you place formulas WITHIN the regex pattern? Also, can the regex pattern handle references to arrays?

@MyOnlineTrainingHub

2 ай бұрын

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. 🤔

@user-ig7ib6dd6e

Ай бұрын

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 ?

@MyOnlineTrainingHub

Ай бұрын

Yes 👍

Any option that array functions could work inside the table as they throw #spill error

@MyOnlineTrainingHub

Ай бұрын

No. There are no plans to support spilled arrays inside Tables.

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

Ай бұрын

I know what you mean. This video covers a few ways you can auto-refresh PivotTables: kzread.info/dash/bejne/Y5hn15Wlna6XnbQ.html

@henryg5735

Ай бұрын

​@@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.

Finally!

@MyOnlineTrainingHub

2 ай бұрын

😁

Only been waiting 30 years for this! Will Excel REGEX support positive/negative look behind/ahead?

@MyOnlineTrainingHub

Ай бұрын

Great to hear, Ian. Yes, it supports positive/negative look behind/ahead. They use the PCRE2 library.

I am using MS 365 - Beta Channel - Version 2406 -(Build 17716.20002) but this function is not available. Why?

@MyOnlineTrainingHub

2 ай бұрын

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.

@MyOnlineTrainingHub

Ай бұрын

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

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? ))

@MyOnlineTrainingHub

Ай бұрын

Not sure as I'm not a RegEx expert, but I can tell you is uses the PCRE2 library if that helps.

@rusektor

Ай бұрын

@@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

@MyOnlineTrainingHub

2 ай бұрын

Thanks for your support 😊

Microsoft have resisted real regex support for decades. Very nice to see useful regex support.

@MyOnlineTrainingHub

Ай бұрын

Indeed! 😊

Got so excited only to realise that I am not one of the chosen few!

@MyOnlineTrainingHub

2 ай бұрын

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! 😅

Sadly, they don't appear in functions list... 😪 (Version 2406 Build 16.0.17716.20002). EDIT: On another computer they DO appear!

@MyOnlineTrainingHub

2 ай бұрын

It took me two PCs to get them too 😅

@rusektor

2 ай бұрын

@@MyOnlineTrainingHub 🤣

I won't have to import resources and write functions to get regex in Excel sheets? Oh, good news!

@MyOnlineTrainingHub

Ай бұрын

Yep 🥳

You should tell, this function is still in Beta and not available for everyone yet at the time you made the video.

@MyOnlineTrainingHub

Ай бұрын

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;-)

@MyOnlineTrainingHub

Ай бұрын

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...?

@Mishkafofer

2 ай бұрын

Excel became a data wrangling tool in the last couple of years. Until then, it was a limited modeling tool for business.

@MyOnlineTrainingHub

Ай бұрын

@montebont, most data experts these days were probably not born until the 90s or later! 😆

@montebont

Ай бұрын

@@MyOnlineTrainingHub which means that they should know about RE's that have been around - and doing a perfect job - for at least 20 years...

@montebont

Ай бұрын

@@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...

@MyOnlineTrainingHub

Ай бұрын

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! :-)

@MyOnlineTrainingHub

2 ай бұрын

😁 so pleased you're excited about these new functions, Geert!

@rusektor

2 ай бұрын

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.

@MyOnlineTrainingHub

Ай бұрын

I know what you mean, Oz 😉 thanks for watching.