7 ms·
Regular expression functions in Excel
- leshenka 2y agotruly amazing times: Excel gets regex support (search-in-selected-cells is not available though, as well as search by regex)
- GregoireDev 2y agoI am confused between all these AI announcements from Microsoft and the exciting new regex support in "preview" (aka soon to be dropped)
- intended 2y agoGood gravy. Things im terrified of: - making an error in a financial model - checking a model - making an error in a regexes - checking a regex Thins I love; - dumb excel stuff This is a great time to be alive, and at arms length from finance. To all the people who will deal with the fallout- I salute you. This is awesome.
- cjk2 2y agoI am terrified of the same things. I got given a validated financial model for something ages ago and found a major Excel fuck up in it. When I say validated, it was run through an expensive validation company who charged a lot of money and they didn't even notice it.
- intended 2y agoOh I know that fear. You can’t trust anything anymore. It’s so easy to just miss something. A date function which is off. Missing something in excel is a big deal for a firm which is mean to do it - and it will always happen. And that’s the experts. I’m betting Someone was too tired, and uninterested and just followed the script. Hopefully that experience put the fear of God into some analyst and associate. Pretty much the only thing that I can rely on when it comes to modeling. Knowing that someone is as terrified as I am and has put the work in to not be embarrassed. And let’s agree - it is work. I remember seeing a date function used by another firm for the first time. It too me at least an hour to decipher it. It was cool, I learnt a lot. But it takes time. Regexes are cool, and I guess people will be learning a lot.
- BitwiseFool 2y agoAlmost a decade ago I worked for a bank and in our little corner of the organization I was rapidly becoming the "Excel Guy". While I was happy to help people, the prospect of being pigeonholed into such a role terrified me and I switched jobs. That wasn't the only reason, but it was a major factor.
- bongodongobob 2y agoThis is why I pretend to not be good at Excel. Once word gets out, it becomes your job.
- Chris2048 2y agoJust to be clear, was the POC financial model implemented in Excel? If so, did it at least have embedded VB or was it all cell logic?
- cjk2 2y agoThere was VBA and cell logic. The sheet was validated by a third party then someone ported it to a proper language (incorrectly). That model was not validated.
- listenallyall 2y agoWhy do you call it "a major Excel fuck up" when the error arose from the language port?
- Chris2048 2y agoDid they not compare data input/output of the excel original versus the port?
- cjk2 2y agoNope
- jeisc 2y agonever too late to do something that should have been since day one
- blitzar 2y agoYet another meta breaker - this is another move aimed at the casual audience to bring in the party gamers and make the program an absolute mess competitively.
- curiousgal 2y agoSolid reference!
- eviks 2y agoCould we get some easy aliasing of REGEXREPLACE to reRepl and picking a regex engine that matches the syntax rules you're used to in a the next decade or so? > Try asking Bing Copilot for regex patterns! Or maybe embed a cheaper and more reliable solution like https://regex101.com https://regex101.com?
- cjblomqvist 2y agoYou could quite easily store a lambda formula (that just passes on the arguments) as a named function. Very neat trick for organizing and re-using formulas in excel.
- underlines 2y agoCoincidentally using the same function names as Google sheets did for years. :D
- filcuk 2y agoWhy would spreadsheet software try to differ in formula names? You want users to be able to switch to your platform with little friction.
- hbossy 2y agoIt used to be the other way around, with Excel leading the way.
- brewdad 2y agoThis is one of those fringe features that 1% of users are clamoring for and 99% of users should never ever use. I’m not surprised it was never at the top of the Excel team’s TODO list.
- IshKebab 2y agoYeah the problem is that those 1% of users are probably writing the most critical spreadsheets, and when companies using GSuite think about moving to Office365 the more pain it is to migrate spreadsheets the worse for Microsoft.
- eb123 2y agoSo why are only 2/3 of the formulas named the same? Excel's new REGEXTEST [1] is REGEXMATCH in Sheets [2] [1] https://support.microsoft.com/en-us/office/regextest-function-7d38200b-5e5c-4196-b4e6-9bff73afbd31 https://support.microsoft.com/en-us/office/regextest-functio... [2]https://support.google.com/docs/answer/3098292?hl=en https://support.google.com/docs/answer/3098292?hl=en
- eb123 2y ago
- Shorel 2y agoI am waiting for the day I can use Excel as a REST API client, so I write the ID of some record in a cell, and Excel does all the work of calling the API repeatedly and fills the rows and sheets with the required information. This will kill a few Python jobs and make it a very popular REST client =)
- actionfromafar 2y agoHow about Excel as an API server, too?
- jodrellblank 2y agoYou've been able to do that for ages, a client automating Excel through COM and listening for events in Excel from script: https://news.ycombinator.com/item?id=37229850 https://news.ycombinator.com/item?id=37229850 Microsoft's advice page for wrapping Office in a web application fronted by ASP/ASP.Net: https://support.microsoft.com/en-us/topic/considerations-for-server-side-automation-of-office-48bcfe93-8a89-47f1-0bce-017433ad79e2 https://support.microsoft.com/en-us/topic/considerations-for... And that has links to documentation about Excel Web Services of old (on-premises SharePoint days, not sure if it translates to today's cloud), which is SOAP/HTTP: https://learn.microsoft.com/en-us/sharepoint/dev/general-development/walkthrough-developing-a-custom-application-using-excel-web-services?redirectedfrom=MSDN https://learn.microsoft.com/en-us/sharepoint/dev/general-dev...
- WorldMaker 2y agoThe "new hotness" for today's cloud are all the Excel APIs in the Microsoft Graph: https://learn.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0 https://learn.microsoft.com/en-us/graph/api/resources/excel?...
- kevin_thibedeau 2y agoMS has had ODBC integration for ages. That is a more rational way to connect Excel with data sources.
- 2y ago
- marcus0x62 2y agoWhat's the joke for this - now you have three problems?
- deleted 2y ago[deleted]
- 6c696e7578 2y agoLibreOffice has had regex for donkeys years.
- dylan604 2y agoI read the title, and was actually surprised how in 2024 this is a headline. I'm surprised that this has not been a feature since forever as well.
- alwillis 2y agoApple’s Numbers spreadsheet has had regex support for a while as well.
- airstrike 2y agowhich is probably the one good thing you can say about Apple's Numbers
- animal_spirits 2y agoI can say the UX of Apple Numbers is the best, much above the usability I've found of Excel. Much less powerful, but it is much easier to use Apple Numbers
- airstrike 2y agoeasier to use for power users or for beginners?
- perryprog 2y agoAs someone who regularly flits between Google Sheets, LibreOffice Calc, and Numbers regularly (with my most time in Google Sheets and Numbers), and as someone who's made some extremely complicated spreadsheets in all three, I have to say I vastly prefer Numbers if I can get away with it. It has a lot of issues that can make some stuff hard fast (no array formulas is a big one), but I find it significantly easier and faster to prototype in Numbers regardless. I think the main bit I love so much about it is having actual tables instead of the Infinite Grid that most spreadsheet software uses. You get named ranges for free, and it makes semantical sense too, among a good number of other benefits (sheet organization, refactoring, simpler styling...). There are some really nice things that Google Sheets does, and I've done a few fancy things with App Script which isn't too bad, and I do really like QUERY though I wish it was a bit higher power. I just always find myself missing the UX of Numbers, though.
- zorrn 2y agoAm I the only one who distrusts domains with numbers in it?
- curiousgal 2y agoNo lookahead/lookbehind? Boo
- neallindsay 2y agoKudos using "regex" instead of "regexp", as it is much nicer to say out loud.
- deleted 2y ago[deleted]
- aronhegedus 2y agohuh, TIL that regex wasn't a thing in vanilla excel. Thought that'd be a basic thing that's included.
- appleiigs 2y agoI realized that long ago... it's incredible that it took this long given how common it is in programming.
- axus 2y agoThe strategy of freezing VBA and BAT scripting, offering better alternatives, and hoping they'd go away on their own hasn't worked.
- wruza 2y agoYou never expect basic ergonomics from an ALL CAPS language. They all suck.
- layer8 2y agoYou can using VBA functions and VBScript.RegExp. This is restricted to Windows though. Microsoft wants to get rid of VBScript, so maybe they are going through the most common use cases in Excel.
- blowski 2y agoHave they officially said they want to get rid of VBScript?
- layer8 2y agoPretty much: https://techcommunity.microsoft.com/t5/windows-it-pro-blog/vbscript-deprecation-timelines-and-next-steps/ba-p/4148301 https://techcommunity.microsoft.com/t5/windows-it-pro-blog/v...
- sebazzz 2y agoYes, but that doesn't include getting rid of VBA. They probably do want to do that though, to allow mobile apps and web-based Excel to run interactive sheets using Javascript. Of course they've been trying for years to allow Javascript in Excel, but at least in the first versions that ran using Internet Explorer in the background. I'm not sure if that has evolved further now.
- against_entropy 2y agoHope the office copilot can do this for me
- louthy 2y agoNow you’ve got two problems
- smartmic 2y agoAny information on which standard they have implemented (POSIX BRE, ERE, PCRE, ...)? Since they are Microsoft, I would not be surprised if there is none.
- shawn_w 2y agoAccording to the documentation links in the article, it's using PCRE2.
- sebsebmc 2y ago> All regular expressions for this function, as well as REGEXEXTRACT and REGEXREPLACE use the PCRE2 'flavor' of regex.
- smartmic 2y agoOkay, thanks! Now next question which comes into my mind: is there info about the regex engine they are using? I would expect there is some (proprietary?) C++ library also used in other MS products or are they even using a FOSS licensed one?
- jandrese 2y agoSeems likely that if they explicitly say they're supporting the PCRE2 syntax it is because they are using the BSD licensed libpcre. Reinventing a regular expression system is very far down on the list of things I'd ever want to do. Those things are filled with dragons and require years of refinement to get the bugs out.
- stockhorn 2y agoNow I can finally parse (X)HTML with Excel ;)
- layer8 2y agoNow I need to build a regex for zalgoifying text in Excel.
- mindaslab 2y agoUse Libre Office if you value your freedom.
- Tronickle 2y agoI love freedom and I love open source, but the problem is that Libre Office is too far from MS Office in terms of UX and functionality. If your usecases are not complicated, Libre office could work well. Otherwise your efficiency will be behind those that you can achieve with MS Office.
- resource_waste 2y agoOh man Libre Office is trash and so are the leadership/maintainers. No tears. I once got banned for asking to put 'text size' on the main screen for the powerpoint knockoff. Text size seems pretty important to have. You shouldn't have to google how to change the text size. I'm deeply convinced there is a Microsoft plant undermining everything.
- zamadatix 2y agoCurrently at least text properties and size is on the main screen in the current defaults for Impress and, just like PowerPoint, only made visible and editable when you click something which has a text size. Maybe it was different in the timeframe you were talking about (barring any actual information on the request) but this combined with how abrasive your comment is leaves little reasoning for why it's horrible and casts serious doubts the ban was actually related to asking about interface enhancements even if that's when you were banned. FWIW I prefer PowerPoint over Impress as well (particularly when it comes to the browser side of things on the go), using one or the other is just not a vendetta of mine.
- resource_waste 2y agoMy comment can make Impress better. Yours continues to enable the status quo. "oh he made a great point, but he said it snarkey! Guess M$ can just keep being the only company with text size"
- personalityson 2y agoThe reason is MS is gradually deprecating VBScript. Today it's the VBScript.RegExp reference that is being used in VBA.
- nickpeterson 2y agoDoes excel have gpu accelerated calculations, seems an obvious thing to add if not?
- layer8 2y agoIt may appear obvious, but only a very limited subset of real-life spreadsheets could meaningfully take advantage of it. Excel does not support GPU-accelerated calculations.
- nickpeterson 2y agodoesnt the back-end of excel have a columnar data model that can store millions of records in a single excel file?
- recursive 2y agoThe front-end of excel doesn't allow the creation of more than 1048576 rows in a sheet. (1<<20)
- smokefoot 2y agoIt’s interesting to see the give and take between google sheets and excel. Google sheets came on the scene shooting for total backwards compatibility and then proceeded to develop some really interesting innovations. Now we see new features emerging on both sides that are quickly replicated by the other player. Notably off the top of my mind: * spill formulas - google first, now supported in MSFT * # notation - MSFT enhancement to spill formulas not yet adopted in google * regular expressions - google first, now in Excel * check boxes - google first, now supported in excel There must be others. I would expect competitive dynamics where each side tries to build extensions that can’t be replicated on the other side
- JoeyBananas 2y agoCongrats on catching up to Perl! Maybe it took so long because Bill was on vacation on Epstein island.
- recursive 2y agoPerl's UI still has some work to do if we're making incomprehensible comparisons.
- tezza 2y agoAt last the production reliability of Excel meets the clarity of regular expressions and the simplicity of matrix slices
- jupin 2y agoFinally, an excel formula I will actually remember