Excel 2010 Bible

Chapter 24: Using Custom Number Formats

567

Here’s another example that displays text before the number:

“Average: “0.00

If you use the preceding number format, you’ll find that the negative sign appears before the text

for negative values. To display number signs properly, use this variation:

“Average: “0.00;”Average: “-0.00

The following format string displays a value with the words Dollars and Cents. For example,

the number 123.45 displays as 123 Dollars and .45 Cents.

0 “Dollars and” .00 “Cents”

Suppressing certain types of entries

You can use number formatting to hide certain types of entries. For example, the following format

string displays text but not values:

;;

This format string displays values but not text or zeros:

0.0;-0.0;;

This format string displays everything except zeros:

0.0;-0.0;;@

You can use the following format string to completely hide the contents of a cell:

;;;

Note that when the cell is activated, however, the cell’s contents are visible on the Formula bar.

The TEXT function accepts a number format string as its second argument. For example, the following

formula displays the contents of cell A1 using a custom number format that displays a fraction:

=TEXT(A1,”# ??/50”)

However, not all formatting codes work when used in this manner. For example, colors and repeating

characters are ignored. The following formula does not display the contents of cell A1 in red:

=TEXT(A1,”[Red]General”)

Using the TEXT Function to Format Numbers