Please note javascript is required for full website functionality.


Final Friday Fix: July 2020 Challenge

31 July 2020

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’s challenge can be answered using Power Query or by using Text to Columns in seconds.  But that’s not what we want here.  The aim is to find a formulaic solution.  That’s a little trickier…

The Challenge

They say sometimes the most complex problems are those that take only moments to communicate.  If that’s true, then this month’s challenge might cause some fun and games!

“All” I want to do is take a text string and split it up so that each element of text is split into a separate column formulaically, e.g.

Note I have used different separators (delimiters) in the two examples displayed.  The fact I have used four elements each time is entirely coincidental.

This month’s challenge is “simply” to come up with a formula that will produce the desired results.

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