Microsoft Office Tutorials and References
In Depth Information
example, the formula in cell C12 uses =TEXT(A12,"m/d/y") to force the date
to display as a date.
The TEXT function gives you a lot of versatility. To learn the custom
formatting codes for a cell, you can select the cell, display the Format
Cells dialog (by pressing Ctrl+1), and select the Custom category on the
Number tab. Excel shows you the codes used to create that format.
If you don ’ t care to learn the number formatting codes, you can use either
the DOLLAR or FIXED function to return a number as text, with a few choices
regarding number of decimals and whether Excel should use the thousands
separator. The formulas shown in C1:C7 in Figure 11.65 return the formatted
text values shown in column B.
The TEXT function converts a value to text in a specific number format. For-
matting a cell with an option on the Number tab of the Format Cells dialog
changes only the format, not the value. Using the TEXT function converts a
value to formatted text, and the result is no longer calculated as a number.
The TEXT function takes the following arguments:
value — This is a numeric value, a formula that evaluates to a nu-
meric value, or a reference to a cell that contains a numeric value.
format_text — This is a number format in text form from the Category
box on the Number tab in the Format Cells dialog. format_textcannot
contain an asterisk (*) and cannot be the general number format.
The DOLLAR function converts a number to text using currency format, with
the decimals rounded to the specified place. The format used is
$#,##0.00_);($#,##0.00). The major difference between formatting a cell that
contains a number with the Format Cells dialog and formatting a number dir-
ectly with the DOLLAR function is that DOLLAR converts its result to text.
A number formatted with the Cells command is still a number. You can contin-
ue to use numbers formatted with DOLLAR in formulas because Microsoft Ex-
cel converts numbers entered as text values to numbers when it calculates.