Excel Function: TEXT

Purpose

The TEXT function converts either a numeric, date or time value to a text value, and changes its appearance such that it adheres to the rules embodied by a format code. Because the output generated by TEXT is always a text value; numeric, date & time operations cannot be performed on it.

Invocations of the TEXT function take the form:

TEXT(value, format_text)

Example

Consider 2 cells containing the formulae;

="The final day of 2024 is " & TEXT(DATE(2024,12,31),"dddd, mmmm dd.")
="The final day of 2024 is " & DATE(2024,12,31)

Both formulae concatenate the text value “The final day of 2024 is ” with the date December 31st, 2024. The first differs from the second through its use of the TEXT function to both convert the date into a text value and ensure its appearance adheres to a format code.

By contrast the second formula makes no attempt to dictate what the appearance of the date will be once it has been converted to a text value and permits Excel to make that decision. Excel converts the numeric value it uses to represent the date into a text value and embeds that in the result. The result is not what one would expect and illustrates why the TEXT function should always be applied to dates, times & numbers before concatenating them with text values. The diagram below illustrates the differences between the 2 formulae & contrasts their output:

Formula 1 applies the TEXT function to a date value prior to concatenating it with "The final day of 2024 is ”. The end result is the text value “The final day of 2024 is Tuesday, December 31.” Note that the date has been formatted according to the format mask “dddd, mmmm dd.” In contrast, formula 2 concatenates a date value with "The final day of 2024 is ”. In this situation Excel converts the date to the text value “45657” and the end result is the text value “The final day of 2024 is 45657”.

Arguments

Argument	Argument type	Description
value	Mandatory	The numeric, date or time value to be converted to a text value.
format_text	Mandatory	A format code which controls the appearance of TEXT’s output.

Related Functions

Function	Example	Purpose
DOLLAR	`DOLLAR(number, decimals)`	Transforms a numeric value into a text value and applies currency formatting rules to it.
VALUETOTEXT	`VALUETOTEXT(value, format)`	Converts a value of any type to a text value.

Error Conditions

Error value	Condition type	Condition
#VALUE!	Runtime	An attempt was made to apply an inappropriate format code to a value.

Examples

Example	Formula	Result	Description
1	`=TEXT(TRUE,)`	`TRUE`	Apply the TEXT function to the logical value TRUE. Do not supply a value to the format_code argument. The text value “TRUE” is returned.
2	`=TEXT(FALSE,)`	`TRUE`	Apply the TEXT function to the logical value FALSE. Do not supply a value to the format_code argument. The text value “FALSE” is returned.
3	`=TEXT(DATE(2028,8,14),"Mmm dd yyyy")`	`Aug 14 2028`	Apply the TEXT function to a date value. Supply the value “Mmm dd yyyy” to the format_code argument. The date is transformed to a text value. Its appearance adheres to the format code.
4	`=TEXT(1.01,"[Red]0.000")`	`1.010`	Apply the TEXT function to a numeric value. Supply the value “[Red]0.000” to the format_code argument. The format code sets the output to be red and have 3 digits in its mantissa. Elements of format codes that set colour have no effect.

Examples: Errors

Example	Formula	Result	Description
1	`=TEXT(3000000, "dd-mm-yyyy")`	`#VALUE!`	The TEXT function was supplied with a format code applicable to dates, but the value supplied could not be interpreted by Excel as being a dates.