Custom Format to show Units without changing Number to Text

Sometimes we need to show the numbers in Units of Measure (e.g., KG, LB, KMS, CM ... etc)

Generally people do this by using Text function but after this, the Number gets converted to Text and it create issues while doing calculations. So to avoid this and still showing the numbers in units, follow the below process:-


1. Select the numbers which you want to show in Units of Measurement

2. Right click and choose "Format Cells" option

3. In the format cells window, go to Number tab and click "Custom" in the category

4. In the type field, enter following:-

0 "KMs"

5. Click OK


You can change the "KMs" to other measurement units of your choice.

Screenshot // Custom Format to show Units without changing Number to Text
Custom Format to show Units without changing Number to Text


  1. "What would be the method to insert a pull-down menu into a cell, allow the user to select a certain item from the list, and then for a comment box to be opened should the user select an item that requires additional documentation/description?

    1. How do I create the pull-down menu?
    2. How do I link a certain item within the pull-down menu so that when it is selected a comments box appears where text could be typed?"

    • Hi,

      I think now you already found your answer because almost a year has been passed. But your questions are exactly I tried to ask in the internet last night.

      1. You can create your own pull-down menu at [Data] -> [Data Validation] -> [setting] -> [Allow] -> [List]. And input your source.
      2. [Home] -> [Conditional formatting], and you can make a rule you want.
      For example, [New Rule] -> [Format only cells that contain] and choose 'specific text' at the first dropdown menu. Input the text contained in your list.

Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.