If you type “14 stone” in the cell, Excel will interpret it as text, and you won’t be able to do any calculations. We need to find another way.
One way is to divide it into two cells. This is not a pretty solution, and I think it should be avoided.
So, let me show you the easy way to do it: Custom number formatting! It’s easy, and it only takes a few seconds.
Select the cells that you want to apply the formatting on and open the Format Cells dialog box (Ctrl+1). Choose Custom, click on ###0 and type your text in double quotes as shown on the picture below.
###0 is a standard number format where the hash (pound sign) represents a significant number and the zero represents an insignificant number. You don’t need to worry about that for now, though!
Here’s all you have to remember: Put the text in double quotes after the format code, like this:
Remember the space after the first quote!
Now it looks a lot better, and you can even increase/decrease decimals without losing your custom text.
This is a very useful feature if you want to show a data table like the one on the picture below. First you set up and calculate the table, then you simply apply custom formatting on each column:
Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.