Introducing REGEX Excel Functions - Extract, Clean, and Format Data Easily! (NEW!)
Рет қаралды 215,136
Күн бұрынJoin 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Regex, or regular expressions, is now available in Excel beta version! This makes it SO much easier to search, match, and manipulate text. (note: currently not all beta users have access. Microsoft is slowly rolling these out.)
⬇️ Download the free RegEx workbook and cheat sheet: pages.xelplus.com/regex-excel...
With regex, you can quickly find all email addresses in a list, reformat dates, or correct typos in a large dataset. You can extract phone numbers from text or change the format of thousands of rows with just a few clicks.
In this video we'll cover two new Excel functions:
▶ REGEXEXTRACT: This Excel function extracts specific text from a string using patterns called regular expressions (regex). It can find and pull the first match, all matches, or parts of the first match.
▶ REGEXREPLACE: This Excel function lets you replace parts of a text string using patterns called regular expressions (regex). This is useful for cleaning up data or making consistent changes across a dataset.
00:00 Regular Expression Regex in Excel
02:37 Extract Email Addresses with Excel REGEXEXTRACT
07:37 Extract Text Inside Parentheses with Excel REGEXEXTRACT
09:27 Extract Dates from Text with Excel REGEXEXTRACT
10:56 Extract Multiple Dates from Text in Excel
11:49 Extract Website URL from Text in Excel
14:31 Split Combined Words in Excel
14:59 Replace Leading Zeros with Excel REGEXREPLACE
16:06 Replace Special Characters with Excel REGEXREPLACE
17:30 Insert a Character at Specific Positions
18:34 RegEx Cheat Sheet
18:47 Wrap up
📨 Subscribe to "Between the Sheets" newsletter to stay on top of office tips and Excel hacks: link.xelplus.com/yt-d-newsletter
➡️ Join this channel to get access to perks:
/ @leilagharani
🎬 LINKS to related videos:
We FINALLY get the New Excel functions we've been Wanting! • Breaking News! We FINA...
New Excel GROUPBY & PIVOTBY Functions: • Excel GROUPBY & PIVOTB...
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
More resources on my Amazon page: www.amazon.com/shop/leilagharani
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel #regex #regularexpressions
@LeilaGharani 25 күн бұрын
Download the Excel workbook and Regex cheat sheet: pages.xelplus.com/regex-excel-file Note: These functions are currently available to some users in the beta channel for Microsoft Insiders. Please be patient as they (often very slowly) roll out to all Office 365 users.
@joerperson 20 күн бұрын
Can I use this function to extract first name, middle initial, and last name from a range where not all records have a middle initial?
@haashini10031985 19 күн бұрын
Thanks so much Leila.
@fellzer 19 күн бұрын
Must be in Excel online. Pass.
@somebody767 17 күн бұрын
@@joerpersonHello joerperson, I have crafted the following regex for you: \b(\p{Lu}\p{Ll}+)\s((?:(?:\p{Lu}(?:\.?\s?)*)\s?)*)(\p{Lu}\p{Ll}+)\b And inside an EXCEL formule: =TOROW(IFERROR(REGEXEXTRACT(A2;"\b(\p{Lu}\p{Ll}+)\s((?:(?:\p{Lu}(?:\.?\s?)*)\s?)*)(\p{Lu}\p{Ll}+)\b";2;0);"")) (maybe you have to replace all ; with , for your region settings of Windows) This regex is looking for 2 words after each other with a capital begin letter and the rest lowercase letters. I have used \p{Lu} instead of [A-Z] and \p{Ll} instead of [a-z], so also names from other langues will be accepted, as long as that language supports capital and lowercase letters.
@lucascrackers 17 күн бұрын
Will this be available on home and student version?
@dustandashes419 25 күн бұрын
I have been wanting REGEX in Excel for forever. I'm retired now, but even so, I will still use it. Back when I was extracting addresses from spreadsheets for databases as a function of my job, this would have been an invaluable tool. AI, REGEX, and Excel. What a time to be alive!
@ashokwwf 24 күн бұрын
Hello sir, Could you please let me know how have you been using REGEX in Excel so far? Did you build any custom VBA functions? Please share your knowledge, it would be very helpful for me.
@katrinabryce 24 күн бұрын
@@ashokwwf At the moment I use Python to do it. Pandas library to open the Excel sheet, the re library to do the regex, and the appropriate database library depending on which database server you are using to write the results to the database. If I wanted to write them to the Excel sheet, then I would probably use xlwings to open and read the sheet, the other libraries like re to do whatever calculations I wanted, and xlwings to write the results back to the spreadsheet.
@angeiring 24 күн бұрын
Finally REGEX came to Excel! I'm using an addin on Excel but I can't use the addin's REGEX inside LAMBDA(), having it natively will help A LOT.
@anil-bhowte 23 күн бұрын
I have been waiting for the same for the last 10 years. I have been using this feature in VBA.
@Tonymrtn 18 күн бұрын
@@katrinabryce I use a simple VBA code to reverse parse this. Basically you dynamic export the parameters to create a full set library and then parse the result to get to the end product. You can also set the import function to do a modular call but its complicated. Once done, depending on our DBMS, we’ll initiate a connection using SQLAlchemy or a comparable ORM to handle the data persistence layer. If we’re looping back to Excel, xlwings will handle the I/O operations, ensuring data integrity while we execute our computation logic
@mrpage221 25 күн бұрын
You're the best for keeping us apprised of all of these new functions. Thank you for all your hard work for our benefit.
@abhishekkuradia898 24 күн бұрын
Finally Regex in Excel, Thank you lord and thank you lelia as always you have the most valuable and up-to-date content.
@chrisouellet2832 22 күн бұрын
yeah! - RegEx is finally here!! thanks for your quick tour Leila! this is just awesome!
@brijkishorkatesaria8208 25 күн бұрын
Thank you Leila for your continuous effort and support
@leo0619 25 күн бұрын
Clear explanations and step-by-step demonstrations made what seemed like a complex topic much more understandable and manageable.Thank you for your dedication to educating and empowering others in the realm of Excel functions.
@LeilaGharani 24 күн бұрын
Many thanks for the kind feedback!
@jagex4me 22 күн бұрын
Was using a combination of FIND, MID, LEN and some more. This is definitely easier. Next is finding a way to have Excel conclude on the nature of a transaction after doing several of these evaluations. Nesting IF statements has its limitations. Thanks for sharing. Now we wait patiently until it is available 😊
@colbyjackson8919 18 күн бұрын
The majority of my excel knowledge I've learned from your videos. Thank you so much. I play with excel for fun and love trying the learning your tips!
@LeilaGharani 17 күн бұрын
Great to hear! Thanks for tuning in.
@ConradWong 25 күн бұрын
It was such a headache to do regex extraction via VBA or Power Query routes. What a wonderful addition.
@jonasfermefors 20 күн бұрын
Absolutely brilliant function. Thank you, Leila, for the video. This one will save me lots of time... when I get it.
@DingusBatus 25 күн бұрын
Interesting, very interesting. Even as a home/hobby Excel user i can see me using this. 😀 I know nothing about REGEX, I had hard of but never seen it. Your explanations was enough as get me started in seeing the patterns of how it works. 😀 Once it’s available to the average user this is going to be worth the learning more about.😀
@ivanbork4175 25 күн бұрын
As always, you take not only a good an interesting object, but You also present it, so it´s to easy understand and use . That being said, I´m starting to be a little critic about MS and their promises of upgrading, it often takes about a year and a half from seeing new object to the actual rollout.
@katrinabryce 24 күн бұрын
Yes, it is probably a good idea to look back at the videos on this channel from about 2 years ago for tips that you can actually implement today.
@GerhardHolzer911 21 күн бұрын
Tolle Hilfen - ich bin schon sehr gespannt, wann es verfügbar sein wird; und danke wie immer für das tolle Video
@vijayarjunwadkar 25 күн бұрын
Thank you Leila, It's really cool to have the regex functions in Excel now and you have done great job in explaining them in the best way as usual. 🙂 Hoping to see more "REGular" videos from you, so I can "EXpress" better and better! 😁
@LeilaGharani 24 күн бұрын
Thank you so much, Vijay! There will be more. 😊
@ThePandaBuffet 16 күн бұрын
oh muh goodness Leila! I don't know if you covered this before. Watching this video and seeing your use of the textjoin function. I decided to use this with the filter function. Using a V/Xlookup function to find a match but only finding one.... You can use the filter function to find many/more/all, but instead of spilling down, you can make a list. Thank you for the inspriation.
@stefaniecalisto 11 күн бұрын
Amazing! I have needed this so much in my apparel analysis where so much info is unstructured text. 🤩
@JonLondrezos 20 күн бұрын
I had to write complicated VBA functions to use the regex libraries before. This is a game changer
@dianew.1568 17 күн бұрын
Thanks for sharing this Leila! It looks awesome! Can't wait for this new feature. I wish it was already out.
@akshaysarkar905 25 күн бұрын
Thank you for this. I hope this will be available to the public very soon.
@balendranrajanathan6808 25 күн бұрын
Fabulous New Function explained expertly and easily
@kadairh 23 күн бұрын
Thank you so much this is awesome! Plus all the other formula keywords I didn’t know existed like TOROW!
@teigenxayden 21 күн бұрын
I have seen many of your course ,each one is excerllent!
@joles72 21 күн бұрын
It is great to finally have this in excel! Thanks for the video!
@robvinje5119 15 күн бұрын
Amazing stuff. Amazing Teacher!! You ROCK Leila!! 👊🏼👊🏼👍🏼👍🏼
@TheDigitalOne 25 күн бұрын
Ehehehe, excellent time saving useful Excel formula turned into an very useful function. Thanks. for the introduction and teaching the new function. The function does seem to have a few glitches here and there, but with a few tweaks here and there the function does seem to work quite fine! 👌🥰✨💎
@fano974 25 күн бұрын
Amazing examples, thank you
@sunshine0282 21 күн бұрын
Another slam dunk... Thank you, life saver!!!
@maneshzaveri5894 23 күн бұрын
Thanks Leila - well explained, hope to use it soon.
@DaveHart-G 24 күн бұрын
Something I do once every couple of years is to create a hypertext linked version of a directory listing of files, starting with the standard output of the command dir / s > dirlist.txt This is then imported into Excel & I then have to systematically review the structure of the paths and separate out information like directory / file, file type, path, filename. Sometimes there is meta-data embedded in the path and file name that are helpful to provide fields that you can filter a long list. Each case has generally had some unique characteristics that mean you can't simply create a standard solution. Regex looks like it might simplify some of the analysis and parsing that I have to do.
@rolexcel 25 күн бұрын
Thanks for this Leila. As an analyst who at times needs to mine large amounts of data that I extract into excel, this will be very useful and save a lot of time. Last year I spent several days mining names, codes and email addresses from a few million random comment records. I have techniques to do this however this would have been more efficient, both faster and easier.
@LeilaGharani 24 күн бұрын
That's a great use case for Regex. 😊
@jrdexterinc 20 күн бұрын
Thanks, Leila, this was very helpful. As usual
@cameronhampton1315 25 күн бұрын
This is amazing. I have a few ideas where this will save me so much time. Great video as always.
@LeilaGharani 24 күн бұрын
Glad you enjoyed it!
@unnikrishnansanthosh 25 күн бұрын
thank you the simple explanation and of course the practice file
@keagankemp6275 23 күн бұрын
I have been waiting for this day, had to watch this vid, i currently use python to use regex now it’s local to excel so distributing reports are now easier
@chongyh2178 25 күн бұрын
Thanks Leila! Tutorial on BREAKING up the combined words in Excel using Regextract is actually not BAD..
@JJ_TheGreat 21 күн бұрын
Haha!!! I see what you did there!!! 😉
@luckymandragoran8471 25 күн бұрын
This just made my day! I lost weeks of work on new formulas because excel crashed yesterday. It also uses regex in office scripts. The rebuild can have this now and it will be way faster too.
@LeilaGharani 25 күн бұрын
Yay! Let‘s hope it gets rolled out soon.
@TRIVIAFOOT977 25 күн бұрын
Thanks a lot. you are always bringing us updates. 👍👍👍
@LeilaGharani 24 күн бұрын
Our pleasure!
@paullaviano4793 5 күн бұрын
Hi Leila, Nice video, just a note you CAN tell it to extract email addresses, you can use name manager :)
@MagdyTawfikA 24 күн бұрын
Thank you Leila for this information, much appreciated!
@LeilaGharani 24 күн бұрын
You're so welcome!
@abdulsamedhabibulla9174 19 күн бұрын
Awesome! Thank you Leila!
@markpodesta4605 25 күн бұрын
Thank you Leila. More magic from Excel! 😀
@LeilaGharani 24 күн бұрын
😁
@alvarorodriguezlasso 22 күн бұрын
Awesome, regards from Cali-Colombia
@tictacsrcool8028 15 күн бұрын
I love the way that u explain and everything can you please do a video about visual basics?
@chh8860 25 күн бұрын
Whoa! ... Leila on Wednesday? ... a surprise ... a very pleasant surprise! Regex appears to be a very powerful function ... I will be a student for quite awhile before I can 'solo' with this function. But I was formulating several ideas during Prof Leila's flawless presentation. Thank you ... thank you ... thank you ... 😍😍😍
@dchall8 24 күн бұрын
It was excellent but I noticed a flaw. At about 16:40 when replacing special characters, the $ in Ver$ion was deleted and not replaced by an s.
@LeilaGharani 24 күн бұрын
Yeah, I got too excited and I didn't want to sit on it for another day. 😁 As always, thanks for tuning in!
@njzzb 25 күн бұрын
This is useful function.Thank you.
@Abdulrahman.Alnahari 22 күн бұрын
Great work Leila thank you for sharing your knowledge.
@LeilaGharani 21 күн бұрын
Glad it was helpful!
@trjozsef 10 күн бұрын
Learned a lot, taking out leading zeroes is however easier with type conversion, =A1+0, probably even if it then needs to be converted back to string (&"").
@jerry5149 25 күн бұрын
Yay! As always, a great presentation!
@chrisreillyfinancialmodeling 23 күн бұрын
I think this is going to be hugely helpful for categorization of bank data in a 13 week cash flow forecast.
@vishalbharakhada3357 24 күн бұрын
Thank u so much. It very useful to me.
@becs-ps1bb 25 күн бұрын
that's a revolution! wow thanks as always Leila!
@LeilaGharani 25 күн бұрын
It really is!
@zzota 25 күн бұрын
Brilliant explanation. Thanks Leila.
@LeilaGharani 24 күн бұрын
Glad you liked it!!
@xyclos 21 күн бұрын
You are fantastic. Thanks so much Leila!!
@LeilaGharani 19 күн бұрын
You're so welcome!
@krzysztofmazurkiewicz5270 25 күн бұрын
Having to play with Regex in C and C++ was fun but here it looks easy ;)
@teoxengineer 7 күн бұрын
I think the purpose of developing this function was to convert the Flash fill static structure into dynamic. It is super handy and useful. Thank you
@BHASKARJOSHI-cw7qn 25 күн бұрын
Love your work
@chrism9037 25 күн бұрын
So awesome Leila, I can't wait to get them!
@LeilaGharani 24 күн бұрын
Thanks, Chris!
@sushantaful 17 күн бұрын
Many many thanks for your nice tutorial videos.
@LeilaGharani 17 күн бұрын
Our pleasure :)
@waaan_ 15 күн бұрын
regex in Excel is game changer
@makboolarakkal6802 25 күн бұрын
Very helpfull function. Great
@ognightrider8730 25 күн бұрын
So cool! Thank you!
@ricigri1242 21 күн бұрын
My nr1 source to the rescue😎. Huge thank you
@SavedbyGraceCT 19 күн бұрын
Thank you, Leila!
@piggybankaccountant 20 күн бұрын
I LOVE the fact that you are using AI to extract the pattern from regex! Feels like advanced excel has been unlocked to the world!
@Nitpickerman 25 күн бұрын
I am glad to see regex in use. Eventhough i do not see that in action for a year or two. Since having unix background I have been using regexp for more than 30 years, so I welcome this. What I am a bit bored about are all ”new features” that cannot be used, as it is not part of regular distributions. Still waiting for instance to be able to start using the checkboxes. I would appreciate if early testers in their channels could remind the excel teams about taking presented features/functions into genaral releases. As said, before it is in general use, it is useless for business use.
@LeilaGharani 25 күн бұрын
I know - the update schedule can be too long depending on your settings. If your tenant is set to semi annual then you really need to wait long….
@JonMotivationalRend 24 күн бұрын
Simply brilliant. I was going to suggest using AI as Regex patterns can be tricky.
@PearsAreOkay 20 күн бұрын
Finally, I get to regex that stage 5 clinger from my life!
@AHMADFAIZSIHAM 25 күн бұрын
Pengetahuan baru, makasih banyak!!!
@learningwithmaliha 17 күн бұрын
Amazing function!
@teigenxayden 21 күн бұрын
You are so great !
@pairenu 24 күн бұрын
Brilliant explain
@alexanderbenson1602 15 күн бұрын
It'll be helpful to use this for giving rid of leading zeroes especially, for me
@gurvindersingharora5301 10 күн бұрын
Hello Sir, Thank you for your useful videos on dax. Sir, when to combine ALL function with Calculate + Filter Function together in Dax ? Could you please make a video of it in detail soon 🙏
@wirechair 18 күн бұрын
been waiting for this since 2020. It is in VBA i think but blow my mind why this was never included from like since 20 years ago. Super useful tool. Anything with using text should come with it honestly. Still no python in excel for regular users!!!
@LeilaGharani 17 күн бұрын
Yeah, Python is still in Beta. It's been some time already. 🙄
@phongluunguyen4177 20 күн бұрын
thank you so much Leila ♥
@LeilaGharani 19 күн бұрын
Our pleasure!
@richardhay645 25 күн бұрын
About time Excel jntrodued These. The Cheat Sheet is valuable but a video on actually writing the code would be helpful. Thanks.
@LeilaGharani 24 күн бұрын
Sure thing. Once Microsoft rolls this out, we'll prepare something.
@danielstober6249 22 күн бұрын
It's so nice to see regex FINALLY supported in Excel! Your presentation was very good, especially in regards to how Excel handles multiple matches -- even as a longtime regex user, I wasn't sure how to deal with that in Excel. I have a couple of suggestions, too: First, you pointed out the "groups" within the pattern when showing how to break up dates into components, but I don't think you were clear enough about how those groups are defined. Second, I think using regex to remove leading zeroes is overkill (at least with the examples you used) since Excel does that by default anyway -- just convert the cell to a number and voilà. I suppose it would be useful if the product number contained any alpha chracters. . Anyway -- great video.
@LeilaGharani 21 күн бұрын
Many thanks for your feedback!
@arthursanyanga105 24 күн бұрын
Wonderfull , thank you
@JimHeil 13 күн бұрын
Great video!!
@LeilaGharani 13 күн бұрын
Glad you enjoyed it
@mohammadj.shamim9342 25 күн бұрын
This is awesome. I was always looking for it. I am handling this situation in R or Python. Glad we have regex in Excel. Leila sensei, on a side note, I guess we both like Breaking Bad 😆.
@LeilaGharani 24 күн бұрын
One of the best. 😊
@ageloskolaitis7821 25 күн бұрын
Thanks for the update. Wish there was an easier way to find the expressions than using AI to find the expression. Like pre-saved expressions in separate formula arguments.
@alaaraad2565 24 күн бұрын
Thank you soooo much
@chrisrucker9975 19 күн бұрын
Great tutorial!
@LeilaGharani 19 күн бұрын
Thank you! Cheers!
@Pityons 22 күн бұрын
Thank you, Leila. 😊
@LeilaGharani 21 күн бұрын
My pleasure :)
@SyedMuzammilMahasanShahi 19 күн бұрын
Thanks for the share.
@LeilaGharani 19 күн бұрын
Our pleasure!
@mythals 25 күн бұрын
I can't wait for this function to be rolled out, it will be a massive time saver.
@LeilaGharani 24 күн бұрын
Definitely. Hopefully it will not take that long.
@asifpervaiz2694 13 күн бұрын
Leila how can I make money with excel . I also have knowledge of excel but don't know where to use my expertise
@sakura0291 25 күн бұрын
OMG, I was just thinking of this while at work today... I had to manually type them T_T
@shahzad10001 25 күн бұрын
Love your work, helps my work!
@didierseverac 25 күн бұрын
I hope they'll add this to power query to !
@maheshnevase9650 15 күн бұрын
Nice video
@maltisent 23 күн бұрын
Hi Leila, I have a question regarding the leading zeros. Why should we use the regexreplace on leading zeros? If we just used the value function it would have the same effect. Easier to understand and consuming less of the CPU. Regarding this.. Most of these examples can be also done using UDFs but having a "official" formula for it may be a lighter solution. Congratulations for the video.
@Satyendra2022 19 күн бұрын
Excellent 👌👌👍👍 work
@LeilaGharani 19 күн бұрын
Thanks a lot 😊
@Satyendra2022 19 күн бұрын
@@LeilaGharani Your All Video 📷 are very awesome 👍😎, I like
@juanes1235 24 күн бұрын
Just, WOW!
@koinguyen6823 24 күн бұрын
Thank Miss Leila
@yousrymaarouf2931 2 күн бұрын
Fantastic
@alinecardoso9668 25 күн бұрын
I wanna change my profession and unfortunately I think I won't use Excel anymore, I have learned to enjoy it, I'll miss it
@LeilaGharani 25 күн бұрын
oh no....
@shinybit 4 күн бұрын
Excel ecosystem is finally catching up to command-line scripting (Linux etc...) which allowed us to do this decades ago.
@toritramel666 25 күн бұрын
Sweet! Thanks for the tips. I used to use it in sheets and was bummed when I went to a company that blocks sheets. Side note: The variation in REGEX pronunciation is kind of like GIF, hotly debated. Although it stands for Regular Expression, many people (myself included) pronounce it with a "J". This is due to the standard rule saying that if a g is followed by an e, i, or y, it's a soft g and says /j/ like "giraffe". But others stand firmly by "It stands for reGular expression so it's G like girl"
@LeilaGharani 24 күн бұрын
Interesting. Thanks for sharing!
@Melds 23 күн бұрын
I've never heard it with a hard G sound before and was wondering if it's a regional pronunciation.
@hemalshahorigamilove 25 күн бұрын
Nice explanation and details. @leila you are always updated. Can we call flash fill as one of the REGEXEXTRACT in built feature? 😊
@LeilaGharani 24 күн бұрын
That would be convenient 😁
Leila Gharani
Рет қаралды 341 М.