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. The input to TEXT must be a numeric value. The output from TEXT is always a text string.

The TEXT function is especially useful when you want to concatenate (join) a formatted number to text. 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 TEXT, you can embed a number in text using exactly the number format needed.

Examples

Below are examples of the TEXT function used to format a value in A1:

=TEXT(A1,"dd-mmm-yy") // date like "15-Oct-19"
=TEXT(A1,"0%") // percent like "25%"
="You won "&TEXT(A1,"$0") // "You won $100"

Notes

Download 100+ Important Excel Functions

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