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

symbol.

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.