Microsoft Office Tutorials and References
In Depth Information
The second argument for the TEXT function consists of a standard Excel number format string.
You can enter any valid number format string for this argument. Note, however, that color codes
in number format strings are ignored.
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”)
In Chapter 6, I discuss how Excel handles dates and times.
The formula might display the following: Report printed on July 22, 2010 at 3:23 PM.
Refer to Appendix B for details on Excel number formats.
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
second argument for the DOLLAR function specifies the number of decimal places.
The following formula returns Total: $1,287.37.
=”Total: “ & DOLLAR(1287.367, 2)