Excel Just Got NEW REGEX Functions You Need to Try!

Рет қаралды 27,033

Күн бұрын

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

Пікірлер: 77

@MyOnlineTrainingHub Ай бұрын

❗NOTE❗These functions are currently available on the Microsoft 365 beta channel, so you may not see them in your version of Excel yet.

@alisongunnels6240 Ай бұрын

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

@MyOnlineTrainingHub Ай бұрын

Great to hear 🙏

@hw7channel571 Ай бұрын

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 🙏😊

@HandelMcHandel Ай бұрын

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 😁

@JimFikes Ай бұрын

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

@MyOnlineTrainingHub Ай бұрын

Glad it was helpful!

@xbia1 Ай бұрын

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

@chrism9037 Ай бұрын

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

@MyOnlineTrainingHub Ай бұрын

Cheers, Chris! Have fun with them 😊

@tmb8807 Ай бұрын

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

@MyOnlineTrainingHub Ай бұрын

One step at a time 😁

@patricknyamu6110 Ай бұрын

Nice thanks

@MyOnlineTrainingHub Ай бұрын

My pleasure 😊

@DingusBatus Ай бұрын

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 Ай бұрын

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

@nevermore17011990 Ай бұрын

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

@MyOnlineTrainingHub Ай бұрын

Glad you can do it all in Excel now 👍

@Milhouse77BS Ай бұрын

Finally!

@MyOnlineTrainingHub Ай бұрын

😁

@sentralorigin Ай бұрын

there is a competitor Excel channel i also watch who dropped a similar video but i much prefer Mynda's

@MyOnlineTrainingHub Ай бұрын

Thanks for your support 😊

@adamnealis Ай бұрын

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

@MyOnlineTrainingHub Ай бұрын

Indeed! 😊

@_logicking Ай бұрын

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

@MyOnlineTrainingHub Ай бұрын

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 25 күн бұрын

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.

@paulvanobberghen Ай бұрын

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 😉).

@stevenlwi1072 Ай бұрын

Is possible to use Regextract to extract date from a string of text ?

@MyOnlineTrainingHub Ай бұрын

Yes 👍

@henryg5735 Ай бұрын

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: kzfaq.info/get/bejne/Z8pjqJad0rGVnYU.html

@henryg5735 29 күн бұрын

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

@AnshulMarele-gbaa Ай бұрын

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.

@andrewdove2303 Ай бұрын

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

@MyOnlineTrainingHub Ай бұрын

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

@ianbennett148 Ай бұрын

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.

@rusektor Ай бұрын

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 29 күн бұрын

@@MyOnlineTrainingHub Yeah, I looked thru function description where it says that. Thanks! )

@akalarun Ай бұрын

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

@MyOnlineTrainingHub Ай бұрын

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!

@njzzb Ай бұрын

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

@gibbousmoon35 Ай бұрын

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

@MyOnlineTrainingHub Ай бұрын

Yep 🥳

@rusektor Ай бұрын

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

@MyOnlineTrainingHub Ай бұрын

It took me two PCs to get them too 😅

@rusektor Ай бұрын

@@MyOnlineTrainingHub 🤣

@user-xm9bh9ck9o 10 күн бұрын

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

@MyOnlineTrainingHub 9 күн бұрын

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.

@montebont Ай бұрын

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.

@montebont Ай бұрын

Amazing how people that call themselves data experts have never heard of RE...Where have they been since the late 1970's...?

@Mishkafofer Ай бұрын

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?

@GeertDelmulle Ай бұрын

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 Ай бұрын

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

@rusektor Ай бұрын

You MUST learn it! It's cool and neat. )))