Final Friday Fix: October 2021 Challenge

On the final Friday of each month, we are 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 are playing for bragging rights only!

This is based upon a real life problem we encountered recently, which was not as easy to solve as thought it would be.  See how you fare.

The Challenge

Sometimes, you may need to deal with numbers in text format.  As you may know, there are various ways to convert them into a numerical data type.  Believe us, this challenge is a bit trickier than the usual problem, but you may come across it some day when using Excel.

One of our clients presented us with a problem similar to the following.  They provided us with a list of numbers copied from their management information system to Excel.  It was in a very strange text format that needed to be converted into numbers.

We have made up a similar list for this challenge that you can download here.

This month’s challenge is to write a formula that may be dragged down to convert the list of numbers in the associated file into a list of “real” numbers, similar to the ones on the right in the image below:

As always, there are some requirements:

• there should be no “helper” column.
• this is a formula challenge: no Power Query / Get & Transform, Text to Columns or Flash Fill! (although you may wish to test these options)
• the formula needs to be dynamic enough so that if we change from four digits to another length, the formula must still work.

We are sure that it may take you a little bit of time (i.e. at least more than three minutes) to figure out a way to clean the list!

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