Microsoft Office Tutorials and References
In Depth Information
Note this revised formula that uses the TEXT function to apply formatting to the value in B3:
=”The net profit is “ & TEXT(B3,”$#,##0.00”)
This formula displays the text along with a nicely formatted value: The net profit is $171,653.
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
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 func-
tion 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, 2013 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 cur-
rency symbol (for example, $).
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)