This week’s tutorial is based on a challenge that was set by a friend a couple of years ago) and follows on nicely from last week’s tutorial where I covered Excel’s CONVERT function which lets you easily convert from one unit to another.

At the start of the pandemic my friend decided to track their weight on a weekly basis. They went out and bought a really smart set of scales, which, at the press of a button recorded and stored permanently inside the device the date and weight. This data could at any time be exported to Excel.

The problem was that it was stored and exported as stones and pounds and my friend wanted it in KG. Unfortunately it wasn’t a simple case of doing a conversion because the data was stored and exported in this format:

12st 10lbs

12st 8lbs

So the challenge was how to convert a text-based value into KG. As with many things in Excel, there’s more than one way to do this. The way I chose to do it was using Power Query.