Flash Fill is a wonderfully clever trick that has been available in Microsoft Excel for Windows since Office 2013. It helps split or combine data that has a simple structure. For example if you had a list of forenames and surnames you could go to the next column and type in a combined version; on the next row, Excel would make a suggestion as you begin typing and, if correct, you can quickly complete the job.

What I wanted to do was to extract forenames and surnames from a list of email addresses. Since they all follow a corporate pattern, it should have been an easy job. Unfortunately, it turns out that it hasn’t been made available on Macs, even with Office 2016. Ho-hum.

I ended up falling back on using the text to columns tool to split away everything from the @ sign and then again to break on the dots. I then copied the forenames and surnames into another pair of columns using the PROPER function (so, for example ‘smith’ becomes ‘Smith’) and then copied those and used Paste-Special to drop the resulting values where I wanted them.

That was certainly a lot quicker than trying to work through the 700-odd item list by hand and probably simpler than trying to use more convoluted formulas to do the work. However, given how excited I was to hear about Flash Fill the other day, I’d love to get to use it on my Mac!