Excel 2010 Bible

Part II: Working with Formulas and Functions

236

The second argument for the TEXT function consists of a standard Excel number format string.

You can enter any valid number format code for this argument.

The preceding example uses a simple cell reference (B3). You can, of course, use an expression

instead. Here’s an example that combines text with a number resulting from a computation:

=”Average Expenditure: “& TEXT(AVERAGE(A:A),”$#,##0.00”)

This formula might return a string such as Average Expenditure: $7,794.57.

Here’s another example that uses the NOW function (which returns the current date and time). The

TEXT function displays the date and time, nicely formatted.

=”Report printed on “&TEXT(NOW(),”mmmm d, yyyy at h:mm AM/PM”)

The formula might display the following:

Report printed on March 22, 2010 at 3:23 PM

Cross-Reference

See Chapter 24 for details on Excel number formats. n

Displaying formatted currency values as text ...........................................................

The Excel 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 func-

tion specifies the number of decimal places):

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

Note
If you’re looking for a function that converts a number into spelled out text (such as “One hundred twelve and
32/100”), you won’t find such a function. Well, Excel does have a function, BAHTTEXT — but it converts the
number into the Thai language. The existence of this function in the English language version of Excel remains
a mystery. n

Repeating a character or string ................................................................................

The REPT function repeats a text string (first argument) any number of times you specify (second

argument). For example, this seasonal formula returns HoHoHo:

=REPT(“Ho”,3)