Monday Morning Mulling: April 2024 Challenge

On the final Friday of each month, set an Excel for you to puzzle over for the weekend.  On the Monday, we publish one suggested solution.  No-one is stating this is the best approach, it’s just the one we selected.  If you don’t like it, lump it – or contact us with your preferred solution.

The Challenge

On Friday, we raised a challenge with an all too familiar example:

You can download the original question file here.  As you will see, in cell F12 (pictured) we had the “pseudo-formula”:

Indeed, the cells F12:F14 are notformatted as Text.  They do not calculate as they are deemed text since they all begin with an apostrophe (‘).  This month’s challenge was to remove all the apostrophes at the same time (without using Power Query, VBA or other similar code).  How did you get on?

Suggested Solution

Your first thought might be to use ‘Replace…’ (CTRL + H) and replace ‘= with =.  Unfortunately, this does not work in all versions of Excel as ‘Replace…’ does not seem to recognise apostrophes in certain instances.

However, there is a very simple trick to circumvent this problem.  With this data still selected, click on the ‘Text to Columns’ button in the ‘Data Tools’ group of the ‘Data’ tab on the Ribbon (ALT + D + E):

This launches the ‘Text to Columns Wizard’ dialog box.  In the first step, ensure that the ‘…file type that best describes your data…’ is set to ‘Delimited’:

Then, believe it or not, simply depress the ‘Finish’ button.  The spreadsheet will then reinstate the formulae, viz.

Simple! 

The solution file can be inspected here.

Word to the Wise

For the second month running, this challenge was incredibly simple if you knew what to do.  Again, we offer no apology because this is a trick you should add to your repertoire, if you’re not already aware of it.

Don’t worry; normal service will be resumed next month.

The Final Friday Fix will return on Friday 31 May 2024 with a new Excel Challenge.  In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business working day.