Formatting numbers – Excel custom numbers format

Excel custom numbers format:YOUR OBJECTIVE

In your Excel table, you created a column in which you recorded Order size. Now you want to format that column so that the abbreviation “pcs” (pieces) appears after every number. In this learning module, you will discover how to create an excel custom numbers format that appends some text to otherwise numeric cell contents.

Excel Custom numbers format:HOW DOES IT WORK?

For well-established units, Excel has a number of predefined number formats available – for a variety of currencies or time formats, for example. But it will sometimes happen that you want to add a non-standard unit symbol or designation to some values in your table. For such situations, you can create your own excel custom numbers format.

For example, in a list of orders, you want to add a units designation to each numerical value in the “Order size” column. You decide that “pcs” (i.e., ‘pieces’) would be satisfactory. You cannot simply type this designation in, because Excel would then consider the affected cell contents to be text and would therefore not be able to process those values in arithmetic calculations. To solve this problem, you can define an excel custom numbers format, which, for the monitor display as well as for printing, appends characters of your choice to the cell contents.

To create an excel custom numbers format, select the relevant cell or range of cells. Then open the Format Cells dialog box by clicking the dialog box launcher on the Home tab, in the Number group. On the Number tab, select the Custom category:

The Sample area shows how the contents of the active cell will be displayed using the currently selected format.

The Type input box shows the currently selected custom format. To append the desired characters (this time, the abbreviated unit), click in the input box just after the displayed format. Then type the desired text. You must enclose the added text in quotation marks.


To prevent your text from butting up against the right-hand cell border, enter a couple of additional spaces after your text.

Conclude your setting by pressing [Enter].

In the current example, the newly created format produces the following result, as pictured below:

You can insert such text before or after the number. But remember this: ding202 always put the respective text in quotes, and ding203 always append (or prepend) the text to the existing number format so that you won’t have to format the number as well.

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Fatal error: Call to undefined function stc_get_connect_button() in /usr/www/users/soluzi/en_excelhelfer/wp-content/plugins/simple-twitter-connect/stc-comments.php on line 231