Ms Office Tune Up

Displaying Formatted Currency Values as Text

Excel’s DOLLAR function converts a number to text using the currency format. It takes two arguments: the number to convert, and the number of decimal places to display. The DOLLAR function uses the regional currency symbol (for example, a $).

You can sometimes use the DOLLAR function in place of the TEXT function. The TEXT function, however, is much more flexible because it doesn’t limit you to a specific number format.

The following formula returns Total: $1,287.37.

The second argument for the DOLLAR function specifies the number of decimal places.

=”Total: ” & DOLLAR(1287.367, 2)

Counting Characters in a String

The Excel LEN function takes one argument and returns the number of characters in the argument. For example, assume that cell A1 contains the string September Sales. The following formula returns 15:

=LEN(A1)

Notice that space characters are included in the character count. This can be useful for identifying strings with extraneous spaces. For instance, if A1 contains Sales and the preceding formula returns 6, there may be a leading or trailing space.

The following formula shortens text that is too long. If the text in A1 is more than ten characters in length, this formula returns the first nine characters plus an ellipsis (133 on the ANSI chart) as a continuation character. If it’s ten or fewer, the whole string is returned:

=IF(LEN(A1)>10,LEFT(A1,9)&CHAR(133),A1)

Popularity: 2% [?]