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
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 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)
Search JabSto ::




Custom Search