Introducing REGEX Excel Functions - Extract, Clean, and Format Data Easily! (NEW!)

แชร์
ฝัง
  • เผยแพร่เมื่อ 24 ธ.ค. 2024

ความคิดเห็น • 432

  • @LeilaGharani
    @LeilaGharani  7 หลายเดือนก่อน +29

    Master Excel with Regex! Download the workbook and cheat sheet here: pages.xelplus.com/regex-excel-file
    Note: These functions are rolling out gradually to Office 365 users, so check your version!

    • @joerperson
      @joerperson 7 หลายเดือนก่อน +2

      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
      @haashini10031985 7 หลายเดือนก่อน

      Thanks so much Leila.

    • @fellzer
      @fellzer 7 หลายเดือนก่อน

      Must be in Excel online.
      Pass.

    • @somebody767
      @somebody767 6 หลายเดือนก่อน

      ​@@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
      @lucascrackers 6 หลายเดือนก่อน

      Will this be available on home and student version?

  • @dustandashes419
    @dustandashes419 7 หลายเดือนก่อน +142

    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
      @ashokwwf 7 หลายเดือนก่อน

      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
      @katrinabryce 7 หลายเดือนก่อน +9

      ​@@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
      @angeiring 7 หลายเดือนก่อน

      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
      @anil-bhowte 7 หลายเดือนก่อน

      I have been waiting for the same for the last 10 years. I have been using this feature in VBA.

    • @Tonymrtn
      @Tonymrtn 7 หลายเดือนก่อน +1

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

  • @qb6959
    @qb6959 25 วันที่ผ่านมา

    This is a GREAT training video!!! Yesterday I got a dataset with 180k rows of data, in which I had to find all “year” numbers in the description column and group them by years. I was going to use filter function to get rid of rows without year information. Now I learned that I can simply use Regxxxx function to achieve my goal. Sincerely thank you ❤

    • @LeilaGharani
      @LeilaGharani  23 วันที่ผ่านมา

      Glad you found something helpful!

  • @mrpage221
    @mrpage221 7 หลายเดือนก่อน +45

    You're the best for keeping us apprised of all of these new functions. Thank you for all your hard work for our benefit.

  • @ivanbork4175
    @ivanbork4175 7 หลายเดือนก่อน +16

    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
      @katrinabryce 7 หลายเดือนก่อน +3

      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.

    • @montebont
      @montebont 5 หลายเดือนก่อน

      Exactly my point

    • @NickCombs
      @NickCombs 2 หลายเดือนก่อน

      As a developer who needs to incorporate lots of product data into software tooling, I gravitate towards CSV and JSON instead of XLSX. The ability to integrate the data into the scripting language of my choice together with the unlimited capability to work with and display that data however I want on a web app or native app is an order of magnitude more powerful than what's possible with Excel or Power BI.

  • @abhishekkuradia898
    @abhishekkuradia898 7 หลายเดือนก่อน +13

    Finally Regex in Excel, Thank you lord and thank you lelia as always you have the most valuable and up-to-date content.

  • @RinrinFibonacci
    @RinrinFibonacci 6 หลายเดือนก่อน +1

    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.

  • @thomass4365
    @thomass4365 10 วันที่ผ่านมา

    Love the Feedback text on Tab 5. Great examples AND a sense of humor.

  • @marcelwehrens9666
    @marcelwehrens9666 หลายเดือนก่อน +1

    Would be a great example to show/remove trailing space(s). They are very hard to find with the eye when debugging strange outcomes! Further excellent explanations, as ever.

  • @JonLondrezos
    @JonLondrezos 7 หลายเดือนก่อน +7

    I had to write complicated VBA functions to use the regex libraries before. This is a game changer

  • @howtolearnexcel
    @howtolearnexcel 6 หลายเดือนก่อน +2

    Oh, so many workarounds, and now REGEX is here. I can't wait for it to be available to most users. Thank you so much for the overview.

    • @LeilaGharani
      @LeilaGharani  6 หลายเดือนก่อน

      You're very welcome!

  • @nassimabed
    @nassimabed 4 หลายเดือนก่อน +1

    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

  • @dbay500
    @dbay500 2 หลายเดือนก่อน +1

    I love that you start with examples. Thank you!

  • @teoxengineer
    @teoxengineer 6 หลายเดือนก่อน +1

    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

  • @chrisouellet2832
    @chrisouellet2832 7 หลายเดือนก่อน +5

    yeah! - RegEx is finally here!! thanks for your quick tour Leila! this is just awesome!

  • @leo0619
    @leo0619 7 หลายเดือนก่อน +3

    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
      @LeilaGharani  7 หลายเดือนก่อน

      Many thanks for the kind feedback!

  • @brijkishorkatesaria8208
    @brijkishorkatesaria8208 7 หลายเดือนก่อน +4

    Thank you Leila for your continuous effort and support

  • @Nitpickerman
    @Nitpickerman 7 หลายเดือนก่อน +1

    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
      @LeilaGharani  7 หลายเดือนก่อน +1

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

  • @skirkbri
    @skirkbri 6 หลายเดือนก่อน

    Thanks!

  • @luckymandragoran8471
    @luckymandragoran8471 7 หลายเดือนก่อน

    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
      @LeilaGharani  7 หลายเดือนก่อน +1

      Yay! Let‘s hope it gets rolled out soon.

  • @DaveHart-G
    @DaveHart-G 7 หลายเดือนก่อน

    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.

  • @Bay_Archer
    @Bay_Archer 7 หลายเดือนก่อน +1

    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
      @LeilaGharani  6 หลายเดือนก่อน

      Great to hear! Thanks for tuning in.

  • @OzduSoleilDATA
    @OzduSoleilDATA 6 หลายเดือนก่อน +1

    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.

  • @rolexcel
    @rolexcel 7 หลายเดือนก่อน

    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
      @LeilaGharani  7 หลายเดือนก่อน +1

      That's a great use case for Regex. 😊

  • @wirechair
    @wirechair 7 หลายเดือนก่อน

    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
      @LeilaGharani  6 หลายเดือนก่อน

      Yeah, Python is still in Beta. It's been some time already. 🙄

  • @LucasNegreiros-k1y
    @LucasNegreiros-k1y 3 หลายเดือนก่อน

    Thanks, you're changing the world with that. Nice work and clear explanations as always. 💚

  • @s2r2420
    @s2r2420 5 หลายเดือนก่อน +1

    Great insight. How much I missed these functions in good old days. Anyway, better late than never. Thanks a ton for this info.

  • @TheReal_iMan
    @TheReal_iMan 4 หลายเดือนก่อน +2

    I like want I'm seeing and I like the you sell the idea first before getting into the details.

  • @ThePandaBuffet
    @ThePandaBuffet 6 หลายเดือนก่อน

    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.

  • @chongyh2178
    @chongyh2178 7 หลายเดือนก่อน +2

    Thanks Leila! Tutorial on BREAKING up the combined words in Excel using Regextract is actually not BAD..

    • @JJ_TheGreat
      @JJ_TheGreat 7 หลายเดือนก่อน

      Haha!!! I see what you did there!!! 😉

  • @jagex4me
    @jagex4me 7 หลายเดือนก่อน +1

    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 😊

  • @vijayarjunwadkar
    @vijayarjunwadkar 7 หลายเดือนก่อน +1

    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
      @LeilaGharani  7 หลายเดือนก่อน

      Thank you so much, Vijay! There will be more. 😊

  • @thebesttechnical3608
    @thebesttechnical3608 27 วันที่ผ่านมา

    Amazing and concise explanation!

  • @Illiakubasov
    @Illiakubasov 3 หลายเดือนก่อน

    Incredible. I can’t think of any document where this would not be useful. Awesome.

  • @Andrea-A5p
    @Andrea-A5p 2 หลายเดือนก่อน +24

    Thanks for this video, it has helped me a lot, but I think I'm going to have to look for an Office key because it tells me that I have disabled functions.

    • @FayeM9
      @FayeM9 2 หลายเดือนก่อน

      BNH Software helped me with my Office key and since then my Excel works without any problem.

  • @stefaniecalisto
    @stefaniecalisto 6 หลายเดือนก่อน

    Amazing! I have needed this so much in my apparel analysis where so much info is unstructured text. 🤩

  • @jfernandez7176
    @jfernandez7176 4 หลายเดือนก่อน

    QUITE SIMPLE! Thank you for sharing this with us. ❤

    • @LeilaGharani
      @LeilaGharani  4 หลายเดือนก่อน

      You are so welcome!

  • @trjozsef
    @trjozsef 6 หลายเดือนก่อน

    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 (&"").

  • @danstober
    @danstober 7 หลายเดือนก่อน

    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
      @LeilaGharani  7 หลายเดือนก่อน

      Many thanks for your feedback!

  • @MrJamesguy1
    @MrJamesguy1 7 หลายเดือนก่อน +1

    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.

  • @chh8860
    @chh8860 7 หลายเดือนก่อน

    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
      @dchall8 7 หลายเดือนก่อน

      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
      @LeilaGharani  7 หลายเดือนก่อน

      Yeah, I got too excited and I didn't want to sit on it for another day. 😁
      As always, thanks for tuning in!

  • @ConradWong
    @ConradWong 7 หลายเดือนก่อน +5

    It was such a headache to do regex extraction via VBA or Power Query routes. What a wonderful addition.

  • @balendranrajanathan6808
    @balendranrajanathan6808 7 หลายเดือนก่อน +1

    Fabulous New Function explained expertly and easily

  • @keagankemp6275
    @keagankemp6275 7 หลายเดือนก่อน

    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

  • @DingusBatus
    @DingusBatus 7 หลายเดือนก่อน

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

  • @jonasfermefors
    @jonasfermefors 7 หลายเดือนก่อน

    Absolutely brilliant function. Thank you, Leila, for the video. This one will save me lots of time... when I get it.

  • @tinhoyhu
    @tinhoyhu หลายเดือนก่อน

    This is very helpful.
    One part I found funny: you wrapped the text without leading zeroes inside VALUE. VALUE by itself on the original text would do the stripping and conversion 😅

  • @zabihafghan6409
    @zabihafghan6409 2 หลายเดือนก่อน

    Your explanation is very well appreciated and I am very keen to follow your all video update which is full of knowledge and guarantee to improve in the professional life. Thanks that we have access to your video subscription

  • @waaan_
    @waaan_ 6 หลายเดือนก่อน

    regex in Excel is game changer

  • @toritramel666
    @toritramel666 7 หลายเดือนก่อน +2

    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
      @LeilaGharani  7 หลายเดือนก่อน

      Interesting. Thanks for sharing!

    • @Melds
      @Melds 7 หลายเดือนก่อน

      I've never heard it with a hard G sound before and was wondering if it's a regional pronunciation.

    • @idjles
      @idjles 6 หลายเดือนก่อน

      gif=graphics, regex=regular. simple, no j's anywhere.

  • @GerhardHolzer911
    @GerhardHolzer911 7 หลายเดือนก่อน

    Tolle Hilfen - ich bin schon sehr gespannt, wann es verfügbar sein wird; und danke wie immer für das tolle Video

  • @MichaelColeman_gig
    @MichaelColeman_gig 6 หลายเดือนก่อน

    I don't have this function yet, and I really need it today. Great tutorial.

  • @Satyendra2022
    @Satyendra2022 7 หลายเดือนก่อน +1

    Excellent 👌👌👍👍 work

    • @LeilaGharani
      @LeilaGharani  7 หลายเดือนก่อน +1

      Thanks a lot 😊

    • @Satyendra2022
      @Satyendra2022 7 หลายเดือนก่อน +1

      @@LeilaGharani
      Your All Video 📷 are very awesome 👍😎, I like

  • @PearsAreOkay
    @PearsAreOkay 7 หลายเดือนก่อน +1

    Finally, I get to regex that stage 5 clinger from my life!

  • @paullaviano4793
    @paullaviano4793 6 หลายเดือนก่อน

    Hi Leila, Nice video, just a note you CAN tell it to extract email addresses, you can use name manager :)

  • @TheDigitalOne
    @TheDigitalOne 7 หลายเดือนก่อน

    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! 👌🥰✨💎

  • @Abdulrahman.Alnahari
    @Abdulrahman.Alnahari 7 หลายเดือนก่อน +1

    Great work Leila thank you for sharing your knowledge.

    • @LeilaGharani
      @LeilaGharani  7 หลายเดือนก่อน

      Glad it was helpful!

  • @ricigri1242
    @ricigri1242 7 หลายเดือนก่อน

    My nr1 source to the rescue😎. Huge thank you

  • @dianew.1568
    @dianew.1568 6 หลายเดือนก่อน

    Thanks for sharing this Leila! It looks awesome! Can't wait for this new feature. I wish it was already out.

  • @robvinje5119
    @robvinje5119 6 หลายเดือนก่อน

    Amazing stuff. Amazing Teacher!! You ROCK Leila!! 👊🏼👊🏼👍🏼👍🏼

  • @krzysztofmazurkiewicz5270
    @krzysztofmazurkiewicz5270 7 หลายเดือนก่อน +1

    Having to play with Regex in C and C++ was fun but here it looks easy ;)

  • @meherdadb
    @meherdadb 2 หลายเดือนก่อน

    Thanks for a very informative session .

    • @LeilaGharani
      @LeilaGharani  2 หลายเดือนก่อน

      Most welcome!

  • @Chihiro33333
    @Chihiro33333 3 หลายเดือนก่อน

    This is exactly what I need! I do hope I have the feature already! 😀

  • @cameronhampton1315
    @cameronhampton1315 7 หลายเดือนก่อน

    This is amazing. I have a few ideas where this will save me so much time. Great video as always.

    • @LeilaGharani
      @LeilaGharani  7 หลายเดือนก่อน +1

      Glad you enjoyed it!

  • @bellepili
    @bellepili 4 หลายเดือนก่อน

    Thank you for this!! ❤ And say hi to Poldi for me 🤗

  • @RandalSullivan-n4b
    @RandalSullivan-n4b 3 หลายเดือนก่อน

    Thank you for the video and the practice file both are and will extremely helpful!😁👍

    • @LeilaGharani
      @LeilaGharani  3 หลายเดือนก่อน

      Glad to hear it!

  • @Autocurator
    @Autocurator 4 หลายเดือนก่อน

    Just impressive 👍🏻

  • @teigenxayden
    @teigenxayden 7 หลายเดือนก่อน

    I have seen many of your course ,each one is excerllent!

  • @joles72
    @joles72 7 หลายเดือนก่อน

    It is great to finally have this in excel! Thanks for the video!

  • @becs-ps1bb
    @becs-ps1bb 7 หลายเดือนก่อน

    that's a revolution! wow thanks as always Leila!

    • @LeilaGharani
      @LeilaGharani  7 หลายเดือนก่อน

      It really is!

  • @jrdexterinc
    @jrdexterinc 7 หลายเดือนก่อน

    Thanks, Leila, this was very helpful. As usual

  • @alvarorodriguezlasso
    @alvarorodriguezlasso 7 หลายเดือนก่อน

    Awesome, regards from Cali-Colombia

  • @piggybankaccountant
    @piggybankaccountant 7 หลายเดือนก่อน +5

    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!

  • @arch.blender1178
    @arch.blender1178 7 หลายเดือนก่อน +3

    Leila the Legend :)

  • @markpodesta4605
    @markpodesta4605 7 หลายเดือนก่อน

    Thank you Leila. More magic from Excel! 😀

  • @bobkaster1
    @bobkaster1 6 หลายเดือนก่อน +1

    Nice to see MS bringing what used to be a VBA only function for Excel out of the code abyss.

  • @sunshine0282
    @sunshine0282 7 หลายเดือนก่อน

    Another slam dunk... Thank you, life saver!!!

  • @zzota
    @zzota 7 หลายเดือนก่อน

    Brilliant explanation. Thanks Leila.

    • @LeilaGharani
      @LeilaGharani  7 หลายเดือนก่อน

      Glad you liked it!!

  • @simplyakshaysarkar
    @simplyakshaysarkar 7 หลายเดือนก่อน

    Thank you for this. I hope this will be available to the public very soon.

  • @xyclos
    @xyclos 7 หลายเดือนก่อน

    You are fantastic. Thanks so much Leila!!

    • @LeilaGharani
      @LeilaGharani  7 หลายเดือนก่อน

      You're so welcome!

  • @abdulsamedhabibulla9174
    @abdulsamedhabibulla9174 7 หลายเดือนก่อน

    Awesome! Thank you Leila!

  • @mochan8447
    @mochan8447 6 หลายเดือนก่อน +2

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

    • @lupocatttivo1478
      @lupocatttivo1478 6 หลายเดือนก่อน

      Oh that is so cool! Thanks Leila and thanks @mochan8447

  • @MagdyTawfikA
    @MagdyTawfikA 7 หลายเดือนก่อน

    Thank you Leila for this information, much appreciated!

    • @LeilaGharani
      @LeilaGharani  7 หลายเดือนก่อน

      You're so welcome!

  • @maneshzaveri5894
    @maneshzaveri5894 7 หลายเดือนก่อน

    Thanks Leila - well explained, hope to use it soon.

  • @kadairh
    @kadairh 7 หลายเดือนก่อน

    Thank you so much this is awesome! Plus all the other formula keywords I didn’t know existed like TOROW!

  • @BrandonFoltz
    @BrandonFoltz 7 หลายเดือนก่อน

    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.

    • @LeilaGharani
      @LeilaGharani  7 หลายเดือนก่อน

      Thanks! Given that this is still in Beta I do expect some improvements going forward.

  • @SolidGearR
    @SolidGearR 6 หลายเดือนก่อน

    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?

  • @adrianogiacomini6541
    @adrianogiacomini6541 5 หลายเดือนก่อน

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

  • @mythals
    @mythals 7 หลายเดือนก่อน

    I can't wait for this function to be rolled out, it will be a massive time saver.

    • @LeilaGharani
      @LeilaGharani  7 หลายเดือนก่อน

      Definitely. Hopefully it will not take that long.

    • @asifpervaiz2694
      @asifpervaiz2694 6 หลายเดือนก่อน

      Leila how can I make money with excel . I also have knowledge of excel but don't know where to use my expertise

  • @diadeorukhkar4556
    @diadeorukhkar4556 6 หลายเดือนก่อน

    Hi, thank you for sharing this! I couldn’t find this in ms excel ms 365 though like you’re using in the excel so easily

  • @RedLongitude
    @RedLongitude 7 หลายเดือนก่อน

    Many many thanks for your nice tutorial videos.

    • @LeilaGharani
      @LeilaGharani  6 หลายเดือนก่อน

      Our pleasure :)

  • @jerry5149
    @jerry5149 7 หลายเดือนก่อน

    Yay! As always, a great presentation!

  • @tictacsrcool8028
    @tictacsrcool8028 6 หลายเดือนก่อน

    I love the way that u explain and everything can you please do a video about visual basics?

  • @Pityons
    @Pityons 7 หลายเดือนก่อน

    Thank you, Leila. 😊

    • @LeilaGharani
      @LeilaGharani  7 หลายเดือนก่อน

      My pleasure :)

  • @JimHeil
    @JimHeil 6 หลายเดือนก่อน

    Great video!!

    • @LeilaGharani
      @LeilaGharani  6 หลายเดือนก่อน

      Glad you enjoyed it

  • @ali.jawwad
    @ali.jawwad 5 หลายเดือนก่อน

    This is really helpful. Thanks for the video.

    • @LeilaGharani
      @LeilaGharani  5 หลายเดือนก่อน +1

      Glad it was helpful!

  • @chrisrucker9975
    @chrisrucker9975 7 หลายเดือนก่อน

    Great tutorial!

    • @LeilaGharani
      @LeilaGharani  7 หลายเดือนก่อน

      Thank you! Cheers!

  • @unnikrishnansanthosh
    @unnikrishnansanthosh 7 หลายเดือนก่อน

    thank you the simple explanation and of course the practice file

  • @conbocuoi22
    @conbocuoi22 7 หลายเดือนก่อน

    thank you so much Leila ♥

    • @LeilaGharani
      @LeilaGharani  7 หลายเดือนก่อน

      Our pleasure!

  • @chrism9037
    @chrism9037 7 หลายเดือนก่อน

    So awesome Leila, I can't wait to get them!

    • @LeilaGharani
      @LeilaGharani  7 หลายเดือนก่อน

      Thanks, Chris!