Exceljet

Quick, clean, and to the point

Excel TEXT Function

Excel TEXT function
Summary 

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

Purpose 
Convert a number to text in a number format
Return value 
A number as text in the given format.
Syntax 
=TEXT (value, format_text)
Arguments 
  • value - The number to convert.
  • format_text - The number format to use.
Version 
Usage notes 

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 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

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.