TEXT Function

The TEXT function returns a number formatted as text, using the number format provided. You can use the TEXT function to embed formatted numbers inside text.

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:

=TEXT(A1,"dd-mmm-yy") // returns"1-Jul-2021"
=TEXT(A1,"mmmm d") // returns "July 1"

With the number 0.537 in cell A1, TEXT can be used to apply percentage formatting like this:

=TEXT(A1,"0.0%") // returns "53.7%"
=TEXT(A1,"0%") // returns "54%"

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 date is "&A1 //  returns "The date is 44378"

The TEXT function can be used to apply date formatting in the final result:

="The date is "&TEXT(A1,"mmmm d") //  returns "The date is July 1"

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