ID EN
…
#Text

TEXT

Excel Functions

Convert a number to text with a number format

Syntax

EXCEL 
=TEXT(value, format_text)

Arguments

Parameter Description
value The number to convert.
format_text The number format to use.

Return Value

A number formatted as text in the given format.

Details

The TEXT function in Excel is a tool for formatting numbers, dates, and times as text. The purpose of the TEXT function is to convert a number to text using a specified format code. TEXT is most often used to control the formatting of a number embedded into a text string. However, TEXT is also a clever way to test dates in more advanced formulas (see below for an example). The TEXT function takes two arguments, value and format_text. Assume cell A1 contains the number 1500.35, and your goal is to display the number as "$1,500.35". You can do that by providing the number format "$#,##0.00" with TEXT like this: The codes provided in format_codes can be adjusted to display decimal numbers, percentages, dates, times, and more. Why do we need the TEXT function? Can't we just apply Excel's built

Examples

Syntax and example

Assume cell A1 contains the number 1500.35, and your goal is to display the number as "$1,500.35". You can do that by providing the number format "$#,

EXCEL 
=TEXT(A1,"$#,##0.00") // returns "$150.35"
Why do we need the TEXT function?

The TEXT function, by contrast, actually converts a number to text. The result is text, so numbers returned by TEXT can't be used in numeric calculati

EXCEL 
="The date is "&A1 // returns "The date is 45585"
Why do we need the TEXT function?

We end up with: "The date is 45585". Why? This happens because the date formatting applied to cell A1 is not part of the number, which is 45585 in Exc

EXCEL 
="The date is "&TEXT(A1,"mmmm d, yyyy")
TEXT with dates

With the date October 21, 2024, in cell A1, the TEXT function can be used like this:

EXCEL 
=TEXT(A1,"dd-mmm-yy") // returns"24-Oct-2024"
=TEXT(A1,"mmmm d") // returns "October 21"
TEXT with dates

The formulas used in column D are below. The result is shown after the "//" marker.

EXCEL 
="The year is "&TEXT($B$5,"yyyy") // "The year is 2024"
="The month is "&TEXT($B$5,"mmmm") // "The month is October"
="The month is "&TEXT($B$5,"mmm") // "The month is Oct"
="The month is "&TEXT($B$5,"mm") // "The month is 10"
="The day is "&TEXT($B$5,"dddd") // "The day is Monday"
="The day is "&TEXT($B$5,"ddd") // "The day is Mon"
="The day is "&TEXT($B$5,"dd") // "The day is 21"
="The day is "&TEXT($B$5,"ddd, mmm d") // "The day is Mon, Oct 21"
TEXT with times

TEXT can format times as well as dates. For example, with the time 3:15 PM in cell A1, the TEXT function can print the time in a 24-hour format like t

EXCEL 
="The time is "&TEXT(A1,"hh:mm") // returns "The time is 15:00"

See Also

DOLLAR FIXED