How to include the measurement unit in the number format in Excel

Excel has a few built-in number formats such as $100.00 or ¥100,00. But what if you want to add another measurement unit to the number format, such as lbs, stone, kg, km/h, ft, acre or oz?

If you type “169 lbs” 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:

But that doesn’t look good. This looks a lot better:

The trick is to use custom number formatting. Select the cells that you want to apply the formatting on and open the Format Cells dialog box (Ctrl+1). Choose Custom, type a 0 followed by the measurement unit in double quotes, for example:

0″ lbs”

Remember the space after the first quote!

That’s it, 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:

Bonus trick:
If you want to show a thousand separator, you should use this format instead:
#,##0″ ft” (common in English-speaking countries)
or
# ##0″ ft” (common in Europe)

More on Text and Formatting:

Today’s Shortcut: Open Format Cells Dialog Box
Highlight Weekends in Excel with Conditional Formatting
The Easiest Way to Hide Zeros in Excel
How to use Excel to Count Characters in a Text String

Are you using a non-English version of Excel? Click here for translations of the 140 most common functions in 17 different languages:

Catalan
Czech
Danish
Dutch
Finnish
French
Galician
German
Hungarian
Italian
Norwegian
Polish
Portuguese (Brazilian)
Portuguese (European)
Russian
Spanish
Swedish
Turkish

Leave a Reply

Your email address will not be published. Required fields are marked *


three + = 12