Regular Expressions Finally Come To Microsoft Excel

May 24, 2024 by Dan Maloney 29 Comments

There are two types of people in the world: those who have no idea what a regular expression is, and those who not only know what they are but can compose them on the fly and tend to use them in situations where they’re clearly not called for. And it’s that latter camp, of which we consider ourself a proud member, that is rejoicing with the announcement that Microsoft is adding regular expression support to Excel.

Or perhaps not rejoicing so much as wondering what took so long. Yes, regular expressions have been part of VBA for a while now, but the new functions allow you to use regexes right in the spreadsheet grid. There are plenty of caveats, of course. The big one is that this is still in beta at this time, so you have to do some gymnastics to enable it, if you’re even allowed to in the first place. Second, support appears limited to three functions at the moment: REGEXTEST, which provides a logical test of pattern matching; REGEXEXTRACT, which returns a substring that matches a pattern; and REGEXREPLACE, which substitutes a string for a pattern. The video below walks through how to use these functions within spreadsheets.

What’s also unclear now is what flavor of regular expressions is supported. There are a bewildering number of entities in the regex bestiary — character classes, positional indicators, quantifiers, subexpressions, lazy and greedy matches, and a range of grouping constructs that perplex even regex pros. One hopes these new functions will support one of the existing regex standards, but Microsoft is famous for “extending and enhancing.” Then again, regex support has been in the .NET Framework for years and is pretty close to the Perl standard, so our guess is that it’ll be close to that.

If you fall into the “What’s a regex?” camp but want to change that, why not get your grep on?

Continue reading “Regular Expressions Finally Come To Microsoft Excel” →

Using Excel To Manage A Commodore 64

June 8, 2023 by Bryan Cockfield 16 Comments

The “save” icon for plenty of modern computer programs, including Microsoft Office, still looks like a floppy disk, despite the fact that these have been effectively obsolete for well over a decade. As fewer and fewer people recognize what this icon represents, a challenge is growing for retrocomputing enthusiasts that rely on floppy disk technology to load any programs into their machines. For some older computers that often didn’t have hard disk drives at all, like the Commodore 64, it’s one of the few ways to load programs into computer memory. And, rather than maintaining an enormous collection of floppy discs, [RaspberryPioneer] built a way to load programs on a Commodore using Microsoft Excel instead.

The Excel sheet that manages this task uses Visual Basic for Applications (VBA), an event-driven programming language built into Office, to handle the library of applications for the Commodore (or Commodore-compatible clone) including D64, PRG, and T64 files. This also includes details about the software including original cover art and any notes the user needs to make about them. Using VBA, it also communicates to an attached Arduino, which is itself programmed to act as a disk drive for the Commodore. The neceessary configuration needed to interface with the Arduino is handled within the spreadsheet as well. Some additional hardware is needed to interface the Arduino to the Commodore’s communications port but as long as the Arduino is a 5V version and not a 3.3V one, this is fairly straightforward and the code for it can be found on its GitHub project page.

With all of that built right into Excel, and with an Arduino acting as the hard drive, this is one of the easiest ways we’ve seen to manage a large software library for a retrocomputer like the Commodore 64. Of course, emulating disk drives for older machines is not uncommon, but we like that this one can be much more dynamic and simplifies the transfer of files from a modern computer to a functionally obsolete one. One of the things we like about builds like this, or this custom Game Boy cartridge, is how easy it can be to get huge amounts of storage that the original users of these machines could have only dreamed of in their time.

AI Creates Your Spreadsheets, Sometimes

August 9, 2022 by Al Williams 9 Comments

We’ve been interested in looking at how AI can process things other than silly images. That’s why the “Free AI Bot that Generates the Excel Formula for Any Problem” caught our eye. Based on GPT-3, it supposedly transforms your problem description into a formula suitable for Excel or Google Sheets.

Our first prompt didn’t work out very well. But that was sort of our fault. When they say “Excel formula” they mean that quite literally. So trying to describe the actual result you want in terms of columns or rows seems to be beyond it. Not realizing that, we asked:

If the sum of column H is greater than 50, multiply column A by 0.33

And got:

=IF(SUM(H:H)>50,A*0.33,0)

A Better Try

Which is close, but not really how anyone even mildly proficient with Excel would interpret that request. But that’s not fair. It really needs to be a y=f(x) sort of problem, we suppose.

Continue reading “AI Creates Your Spreadsheets, Sometimes” →

Excel Ray Tracing With Help From C

September 28, 2021 by Al Williams 12 Comments

It isn’t news that [s0lly] likes to do ray tracing using Microsoft Excel. However, he recently updated his set up to use functions in a C XLL — a DLL, really — to accelerate the Excel rendering. Even if ray tracing isn’t your thing, the technique of creating custom high-performance Excel functions might do you some good somewhere else.

We’ve seen [s0lly’s] efforts before, and you can certainly see that the new technique speeds things up and produces a better result, which isn’t especially surprising. In addition to being faster, the new routines produce more detail.

Continue reading “Excel Ray Tracing With Help From C” →

Little Hex Tricks Make Little Displays A Little Easier

January 24, 2020 by Kerry Scharfglass 16 Comments

Depending on the device in hand and one’s temperament, bringing up a new part can be a frolic through the verdant fields of discovery or an endless slog through the grey marshes of defeat. One of the reasons we find ourselves sticking with tried and true parts we know well is that interminable process of configuration. Once a new display controller is mostly working, writing convenience functions to make it easier to use can be very satisfying, but the very first thing is figuring out how to make it do anything at all. Friend of Hackaday [Dan Hienzsch] put together a post describing how to use a particular LED controller which serves as a nice walkthrough of figuring out the right bitmath to make things work, and includes a neat trick or two.

The bulk of the post is dedicated to describing the way [Dan] went about putting together his libraries for a 7-segment display demo board he makes. At its heart the board uses the IS31FL3728 matrix driver from ISSI. We love these ISSI LED controllers because they give you many channels of control for relatively low cost, but even with their relative simplicity you still need to do some bit twiddling to light the diodes you need. [Dan]’s post talks about some strategies for making this easier like preconfigured lookup tables with convenient offsets and masking bits to control RGB LEDs.

There’s one more trick which we think is the hidden star of the show; a spreadsheet which calculates register values based on “GUI” input! Computing the bit math required to control a display can be an exercise in frustration, especially if the logical display doesn’t fit conveniently in the physical register map of the controller. A spreadsheet like this may not be particularly sexy but it gets the job done; exactly the kind of hack we’re huge fans of here. We’ve mirrored the spreadsheet so you can peek at the formulas inside, and the original Excel document is available on his blog.

Experiments In 3D Graphics Via Excel

August 29, 2019 by Lewin Day 10 Comments

3D graphics were once the domain of university research groups and large, specialized computing systems. Eventually, they were tamed and became mainstream. Your phone, tablet, and home computer are all perfectly capable of generating moving 3D graphics. Incidentally, so is Microsoft Excel.

This is the work of of [s0lly], who has been experimenting wtih Excel in this way for quite some time. Starting with pseudo-3D graphics, the project then progressed to the development of a real 3D engine. Naturally, things couldn’t stop there. The next logical step was to advance to raytracing, which was pulled off with aplomb. Shiny spheres on featureless planes are par for the course here.

The graphics are necessarily basic, with resolutions on the order of 256×144. Output is by changing the individual color of the various cells of the spreadsheet. The relevant files are available on Github, for those eager to tinker with experiments of their own. We’ve seen others attempt similar work before, with [C Bel] writing a full game engine for the platform. Video after the break.

Continue reading “Experiments In 3D Graphics Via Excel” →

Images As Excel FIles Are Gloriously Nasty

March 1, 2018 by Jenny List 58 Comments

Almost every person of a technical persuasion who has worked in an office will have some tale of wildly inappropriate use of office technology for a task that could have been accomplished far more simply with an appropriate tool. There are jokes about people photocopying a blank sheet of paper when they need a few sheets themselves, but some of the real stories are very bit as surreal.

[Bjonnh]’s patience for such things was exceeded when he received a screenshot embedded in a Microsoft Word file. His response is both pointless and elegant, a Python script that takes a JPEG image and encodes it into an Excel file. It’s simply an array of cells whose background colours represent the pixels, and he warns us that the output files may take a while to load. We just had to subject it to a test, but are sorry to report that LibreOffice doesn’t seem to want to play ball.

So yes, this is a small departure from our usual fare of hardware, and it serves no use other than to be a fantastically awful misuse of office technology. If you’ve ever been emailed a PowerPoint invitation to the office party though, then maybe you’ll have cracked a smile.

If pushing your corporate spreadsheet to the limit is your thing, perhaps you’d also like to see it running a 3D engine.