Microsoft Office Tutorials and References
In Depth Information
Using the TEXT function
Using the TEXT function
Another solution uses the TEXT function, which displays a value by using a specified number format:
=TEXT(A1,”””Total: “”$#,0.00”)
This formula returns something like this:
Total: $1,594.34
The second argument for the TEXT function is a number format string — the same type of string that
you use when you create a custom number format. Because the number portion is formatted, this
approach looks good. But besides being a bit unwieldy (because of the extra quotation marks), this
formula suffers from the same problem mentioned in the previous section: The result is not numeric.
Using a custom number format
If you want to display text and a value — and still be able to use that value in a numeric formula —
the solution is to use a custom number format.
To add text, just create the number format string as usual and put the text within quotation marks.
For this example, the following custom number format does the job:
“Total: “$#,0.00
Even though the cell displays text, Excel still considers the cell contents to be a numeric value.
Therefore, you can use this cell in other formulas that perform calculations.
Search JabSto ::

Custom Search