Excel Functions and Formulas, TEXT()
The TEXT() function converts a number value to a text string, with the format specified
- Example 1, Converting Numeric Value to a Text String Using Currency Formatting
- Example 2, Adding Converted Value to a Text String
- Example 3, Converting Numeric Value to a Text String Using Decimal Formatting
- How to Get the Formatting String
Value: The number value to convert to a text string.
Format: The formatting to apply to the converted value.
In this example the value in cell A1 is converted to a text string with the currency formatting. Write the following formula in cell B1:
In the previous example you might be wondering what was the use of converting the numeric value to a text string? We could have easily just used the formula:
in cell B2, and just formatted the cell to have the currency formatting and the same result would have been obtained?
Answer: The reason for converting numeric values to a text strings is to be able to add (concatenate) them to other text string values while preserving their number format. For more information about adding (concatenating) text strings please see Excel Functions and Formulas, Adding (Concatenating) Text. For example lets say we want to create the text string:
“John has spent ____ on buying a new house”
and replace the underline with the money value in cell B1. This can only be done if the value in cell B1 is a text string. Add the formula below to cell C1:
By changing the value in cell A1, The text in cell C1 will update automatically:
Write the following formula in cell B1:
This formula will convert the numeric value in cell A1 to a text string with the decimal formatting in cell B1:
In the first example I used the formatting string below to apply the currency formatting to the converted numeric text string:
In the second example I used the formatting string below to apply the decimal formatting to the converted numeric text string:
So where did I get these from? How can you figure out what formatting string to use?
Answer: Right click any cell and click on Format Cells …:
From the number tab select your desired formatting from the list of available formatting options:
- Excel Functions and Formulas, Adding (Concatenating) Text
- Excel Functions and Formulas Tutorial / Sample, Text and String #2 (Advanced)