Syntax. TEXT(value,format_text)
Definition. This function converts a value into text in a specific number format.
Arguments
value (required). A number, a formula that evaluates to a numeric value, or a reference to a cell containing a numeric value
format_text (required). A number format, which is one of those in the Custom category box on the Number tab in the Format Cells dialog box
Background. You might need to convert numeric values to text to link static text with calculations. The TEXT() function not only converts numeric values to text but also allows you to use the number formats available in the Format Cells dialog box.
In the format_text argument you can specify custom formats. However, the formats have the following restrictions:
Formats cannot contain an asterisks (*).
The General number format is not allowed.
Colors, such as red for negative values, are ignored.
The difference between the Format/Cells command and the TEXT() function is that TEXT() returns text. A number formatted with the Format/Cells command is still a numeric value. You can still use numbers converted with the TEXT() function in other formulas, because for calculations Excel converts numbers displayed as text values into numeric values.
Example. Assume that you want to include a dynamic date of payment in an invoice form, so that 14 days are added to the current date. The formula
=CONCATENATE("Please pay before ",TEXT(TODAY()+14,"MM/DD/YYYY"),".")returns Please pay before 12/15/2010 on December 1, 2010. The formula
=CONCATENATE("Please pay before ",TODAY()+14,".")without the TEXT() function also works, but your customer probably wouldn’t know what to do with Please pay before 40527.
On another form, assume that you want to show the current date in a sentence. The formula
="Today is "&TEXT(TODAY(),"DDDD")&", "&TEXT(TODAY(),"MMMM D. YYYY")&"."
results in Today is Monday, December 1, 2010 on December 1, 2010.