The Excel TEXT function enables you to display a value in a specific number format. Although this function may appear to have dubious value, it does serve some useful purposes, as the examples in this section demonstrate. Figure 5-2 shows a simple worksheet. The formula in cell D1 is
Figure 5-2: The formula in D1 doesn’t display the formatted number.
="The net profit is " & B3
This formula essentially combines a text string with the contents of cell B3 and displays the result. Note, however, that the contents of B3 are not formatted in any way. You might want to display B3’s contents using a currency number format.
| Note |
Contrary to what you might expect, applying a number format to the cell that contains the formula has no effect. This is because the formula returns a string, not a value. |
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 $104,616.52.
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.
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 July 22, 2004 at 3:23 PM.
| Cross Ref |
Refer to Appendix B for details on Excel number formats. |
Popularity: 2% [?]
Recent Comments