The TEXT function takes two arguments, value and format_text. Value is the number to be formatted as text and should be a numeric value. If value is already text, no formatting is applied. Format_text is a text string that contains the number formatting codes to apply to value. Supply format_text as a text string enclosed in double quotes (""). To see examples of various number format codes, see Excel Custom Number Formats. Note: The output from TEXT is always a text string. To format a number and maintain the numeric value, apply regular number formatting. The TEXT function is useful when concatenating a formatted number to a text string. For example, “Sales last year increased by over $43,500”, where the number 43500 has been formatted with a currency symbol and thousands separator. Without the TEXT function, the number formatting will be stripped. This is especially problematic with dates, which appear as large serial numbers. With the TEXT function, you can embed a number in text using exactly the number format needed.
Examples
With the date July 1, 2021 in cell A1, the TEXT function can be used like this: With the number 0.537 in cell A1, TEXT can be used to apply percentage formatting like this: The TEXT function is especially useful when concatenating a number to a text string with formatting. For example, with the date July 1, 2021 in cell A1, concatenation causes date formatting to be removed, since dates are numeric values: The TEXT function can be used to apply date formatting in the final result:
Notes
Value should be a numeric value Format_text must appear in double quotation marks. The TEXT function can be used with custom number formats
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
