Microsoft Office Tutorials and References
In Depth Information
Understanding text functions
For information about formatting symbols and codes, see Table 9-1, “Custom format
symbols,” and Table 9-2, “Built-in custom format codes,” both in Chapter 9.
The DOLLAR function
Like the TEXT function, the DOLLAR function converts a number into a string. DOLLAR,
however, formats the resulting string as currency with the number of decimal places you
specify. The arguments ( number, decimals ) specify a number or reference and the number
of decimal places you want. For example, the formula =DOLLAR(45.899, 2) returns the text
string $45.90 . Notice that Excel rounds the number when necessary.
If you omit decimals , Excel uses two decimal places. If you add a comma after the first
argument but omit the second argument, Excel uses zero decimal places. If you use a negative
number for decimals , Excel rounds to the left of the decimal point.
The LEN function
The LEN function returns the number of characters in an entry. The single argument can
be a number, a string enclosed in double quotation marks, or a reference to a cell. Trailing
zeros are ignored. For example, the formula =LEN("Test") returns 4 .
The LEN function returns the length of the displayed text or value, not the length
of the underlying cell contents. For example, suppose cell A10 contains the formula
=A1+A2+A3+A4+A5+A6+A7+A8 and its result is the value 25. The formula =LEN(A10)
returns the value 2 , which indicates the length of the resulting value, 25 . The cell referenced
as the argument of the LEN function can contain another string function. For example, if
cell A1 contains the function =REPT("–*", 75), which enters the two-character hyphen and
asterisk string 75 times in a cell, the formula =LEN(A1) returns the value 150 .
The ASCII functions: CHAR and CODE
Every computer uses numeric codes to represent characters. The most prevalent system of
numeric codes is ASCII, or American Standard Code for Information Interchange. ASCII uses
a number from 0 to 127 (or to 255 in some systems) to represent each number, letter, and
The CHAR and CODE functions deal with these ASCII codes. The CHAR function returns the
character that corresponds to an ASCII code number; the CODE function returns the ASCII
code number for the first character of its argument. For example, the formula =CHAR(83)
returns the text S . The formula =CODE("S") returns the ASCII code 83 . If you type a literal
character as the text argument, be sure to enclose the character in quotation marks;
otherwise, Excel returns the #NAME? error value.
Search JabSto ::

Custom Search