Final Friday Fix: September 2020 Challenge

On the final Friday of each month, we’re going to set an Excel / Power BI challenge for you to puzzle over so that you can get your “Excel fix”.  Challenge your office colleagues to see who can solve the puzzle quickest.  There are no prizes at this stage: you’re playing for bragging rights only!

This month, we look at a common “clean up” problem in Excel – how to separate text and numbers.

The Challenge

Often, we receive data that isn’t in the best of shape.  It may have text and numbers intermingled and you wish to separate (i.e. space) them out.  For example, you might want “Pelham123” to be “Pelham 123”, or require “121Training” to be “121 Training”. 

Where there is just the one change of data type in the text string, it’s possible to create a reasonably straightforward formula, but things get harder with multiple occurrences.  And that’s what this month’s challenge is – fix the issue in general.

Using your favourite search engine will typically yield third party add-ins, VBA (e.g. User Defined Functions) and Power Query solutions – all NOT allowed here!

This month’s challenge is to construct an Excel formula that will split out multiple occurrences of numbers and text, e.g.

Sounds easy? Then why not have a go? We’ll publish one solution in Monday’s blog. Have a great weekend in the meantime!