Microsoft Office Tutorials and References
In Depth Information
Changing Text
These patterns are created with the use of a few key symbols. A pound sign
(#) is a placeholder for a number — that is, a single digit. Interspersing
pound signs with fixed literal characters (such as a dollar sign, a percent
sign, a comma, or a period) establishes a pattern. For example, this pattern —
$#,###.# — says to display a dollar sign in front of the number, to use a
comma for a thousands separator, and to display one digit to the right of
the decimal point. Some formatting options used with the TEXT function are
shown in Table 16-1. Look up custom number formatting in Excel Help for
more information on custom format patterns, or go to www.microsoft.com
and search for guidelines for custom number formats.
Table 16-1
Formatting Options for the TEXT Function
Format
Displays
=TEXT(1234.56,”#.##”)
1234.56
=TEXT(1234.56,”#.#”)
1234.6
=TEXT(1234.56,”#”)
1235
=TEXT(1234.56,”$#”)
$1235
$1,235
=TEXT(1234.56,”$#,#”)
$1,234.56
=TEXT(1234.56,”$#,#.##”)
40%
=TEXT(0.4,”#%”)
=TEXT(“3/15/2005”,”mm/dd/yy”)
03/15/05
=TEXT(“3/15/2005”,”mm/dd/yyyy”)
03/15/2005
=TEXT(“3/15/2005”,”mmm-dd”)
Mar-15
Figure 16-6 shows how the TEXT function is used to format values that are
incorporated into sentences. Column C contains the formulas that use TEXT.
For example, C4 has this formula: =“We spent “ & TEXT(B4,”$#,#.#0”)
& “ on “ & A4. Cell C8 has this formula: =“We opened the office on
“ & TEXT(B8,”mmm d, yyyy”).
Figure 16-6:
Using TEXT
to report
in a
wellformatted
manner.
Search JabSto ::




Custom Search