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.
@@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.
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!
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.
@@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.
@@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
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.
Before that I used nested FIND and LEFT and RIGHT and MID and these formulae were complex, all leveraging the pattern to build logical extraction tools around it. Now one function does it. Sweet
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.
I like this. Very helpful. I heard of REGEX 10 years ago and it freaked me out. This video and Victor Momoh's video have put me at ease to give this a serious look.
Amazing. I wish this had existed 10 years ago as these examples are *exactly the kinds of things I would have to do regularly and figure out how to do with older functions or code. You explanations and lessons are very well put together. But this is the first one that I actually did download your very helpful lesson material. Thank you.
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 😊
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.
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.
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.
Excellent share thanks! In tab 3 you have dragged down the same formula in C2:C11 so it only works at the row level and outputs at the row level. Here is a single cell version of the same formula that processes the entire B2:B11 range at once and outputs a dynamic array from C2:E11 with one single formula: =LET( dates, BYROW(B2:B11, LAMBDA(row, TEXTJOIN("-", TRUE, IFNA(REGEXEXTRACT(row, "(\d{4})-(\d{1,2})-(\d{1,2})"), {"","",""})))), splitDates, TEXTSPLIT(TEXTJOIN("-", TRUE, dates), "-"), INDEX(splitDates, SEQUENCE(ROWS(B2:B11), 3)) ) Similarly in tab 7 you can use the following single cell formula in C2: =BYROW(B2:B11, LAMBDA(row, TEXTJOIN(" ", TRUE, REGEXEXTRACT(row, "([A-Z][a-z]+)", 1)))) and the following single cell formula in B2 in tab 10: =REGEXREPLACE(A2:A6,"(.{4})(?=.{4})","$1-")
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"
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! 😁
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.
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.
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!!!
yeeeee!!!!!! Finally I can remove the morefunc library from my pc and can install excel 64 bit!!!! I have been waiting for this moment for almost 15 years 🥳🥳🥳🥳🥳🥳🥳🥳🥳🥳🥳
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.
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.😀
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 (&"").
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 ... 😍😍😍
Great video. I would put this into the cool and necessary but still half-baked category. Hopefully it will improve. If we need to go out and find REGEX patterns, add additional functions to control spill direction, etc. it's still clunky in 2024 and the age of AI.
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 🙏
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
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! 👌🥰✨💎
Love it. I only find it very frustrating that Microsoft takes for ever to roll out the new stuff. Still waiting for tick box option and even PIVOT formulas.
I see why Microsoft didn't introduce REGEX until after they came out with CoPilot! I have a programming background, and even for me, having to construct one of those "regular expressions" on my own would be sufficiently daunting that I wouldn't consider it worth the effort unless I had a very large amount of data to process or had to parse text like this frequently. Even just trying to modify or debug one looks pretty challenging. At least with AI on hand to provide the regular expressions, REGEX becomes accessible. Still, I suspect anyone who doesn't spend a lot of time writing Excel formulas will just shake his head at this and move on. You did a terrific job explaining and demonstrating it, though.
Hey Leila, when the heck is MS going to put some animations in Excel? I can imagine charts and 'floating' tables flying into place with the click of a slicer! Can you imagine the interactive dashboards?! Maybe they'll listen to you if you start bugging them for it. Thanks for everything you do!
Hi Leila, I'm being watching your videos and learning. I am a beginner and I love excel. My excel knowledge has gone down drastically due to lack of practise and in my work we don't use excel too much. Just for the basic needs! I have an interesting scenario where we maintain data for all incidents and track those incidents and close them before the deadline. All open incidents are treated based on he days open like if an incident is above 25 days that needs attention. So the chart I created needs a conditional formatting but the challenge is I use the filter function to retrieve all open incidents based on status =open. I have divided the chart is 3 categories like 1015 days. But I am unable to create a dynamic chart with those fields and I ending up nowhere. Do you have a solution for this ? My dataset would be (Header = Events, Day15 and above, Day 10 and
Hi, been searching for excel gods in youtube and by far you are the best! Can you help me direct what functions do I use in my scenario. I have multiple entry forms to be filled out by different departments (data collection stage). Once filled up, I copy and paste it on my masterlist. Any suggestions on what function I use so that the multiple entry forms autopopulate my masterlist in a chronological order. Dept.A and Dept. B, etc can do different task on the same day, meaning on my masterlist I manually sort the task by date (using the Sort & filter). And to top it all of, once data is entered in the master list, it has a unique transaction code which sometimes gets broken when some departments submit late and antidated reports. Any leads on this on what functions I may use to dynamically auto populate my masterlist based on the data across multiple entry forms in Chronological Order. Thank you soo much.
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.
At 15:00 I personally find it a lot easier to simply multiply by 1. This deletes all the leading zero's and makes it a value. However, this is only possible if there is no other text.
It's great news, something that should have been there for so long. I used vba in the past to create my regex function, but there are always limitations to vba...
Hi! Leila I hope you can answer this question? For the pivot table. Why is the amount in the "Value" field different than if you put it in the row field?
It was great! 👍 I was very wrong by being against the use of AI! We must adapt ourselves according to the times we are living in! I have also started using Chat GPT for work purposes and will also use other AI tools, applications and features as per requirements 🙂
17:26 This is good stuff - I was hoping that you’d have an example of how to “un1337” the text, so the $ would resolve to an S, and we would get the intended “Version” instead of “Verion“ on row 3.