Final Friday Fix: April 2024 Challenge

On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI problem for you to puzzle over for the weekend.  On the Monday, we publish a solution.  If you think there is an alternative answer, feel free to email us.  We’ll feel free to ignore you.

The Challenge

Last month, we presented a problem which revolved around recognising a cell had been entered as text.  This month, that’s a given.

Consider the following example (you might find it rather familiar!):

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

This month, 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 is again simple but is a necessary skill for modelling.  What is the quickest way to remove all the apostrophes at the same time (without using Power Query, VBA or other similar code)?

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