TEXT Function

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

Syntax and example

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. Most often, this is a cell reference like A1. Note that the value must be a number for TEXT to do anything. If the value is already text, no formatting will be applied.
• Format_text is a text string that contains the format codes to apply to value. Note that format_text must be enclosed in double quotes (""), and must contain valid number format codes.

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:

=TEXT(A1,"$#,##0.00") // returns "$150.35"

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?

Why do we need the TEXT function? Can't we just apply Excel's built-in number formatting to format numbers in a worksheet? Yes. In general, you should always try to use regular number formatting first because it preserves the numeric value underneath. Keeping the number allows it to be used in standard numeric calculations.

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 calculations. However, there are still many situations where TEXT is quite helpful. The most common example is when you need to embed a number inside a text string. For example, assume we have the date 20-Oct-2024 in cell A1 and want to display a message like "The date is October 20, 2024". If we simply concatenate the text to cell A1 like this:

="The date is "&A1 // returns "The date is 45585"

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 Excel's date system. The formatting is not available during concatenation. To include a formatted date in a text string, we need to use the TEXT function to control formatting like this:

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

This formula returns the date in a readable format like "The date is October 20, 2024".

TEXT with dates

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

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

In the worksheet below, you can see how the TEXT function can be used to apply a variety of date formats to the date in cell B5:

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

="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 this:

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

In the worksheet below, TEXT is configured to display the time in cell B5 in various ways:

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

="The hour is "&TEXT($B$5,"h AM/PM") // "The hour is 3 PM"
="The hour is "&TEXT($B$5,"hh") // "The hour is 15"
="The minutes are "&RIGHT(TEXT($B$5,"hhmm"),2) // "The minutes are 15"
="The AM/PM is "&TEXT($B$5,"AM/PM") // "The AM/PM is PM"
="The time is "&TEXT($B$5,"hhmm") // "The time is 1515"
="The time is "&TEXT($B$5,"hh:mm") // "The time is 15:15"
="The time is "&TEXT($B$5,"h:mm AM/PM") // "The time is 3:15 PM"

Note that the formula in D7 is not like the others. Because the code "mm" conflicts with the date formatting codes for "month" when used alone, we use the format code "hhmm". Then we use the RIGHT function to extract just the two characters to the right, which are minutes.

TEXT with percentages

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

In the worksheet below, we use the TEXT function together with the IFS function to report the gain or loss as a percentage in column F. The formula in F5, copied down, looks like this:

=IFS(D5>0,"Up "&TEXT(D5,"0.0%"),D5<0,"Down "&TEXT(D5,"0.0%"),D5=0,"Unchanged")

The IFS function is used to control the flow of the formula by testing for three conditions. If the change is greater than zero, IFS returns:

"Up "&TEXT(D5,"0.0%")

If the change is less than zero, IFS returns:

"Down "&TEXT(D5,"0.0%")

Finally, if the change equals zero, IFS returns the message "Unchanged":

"Unchanged"

The result in column F is that each row has a custom message that depends on whether the change is positive, negative, or zero.

Using TEXT in other formulas

The TEXT function is surprisingly versatile and turns up in many other advanced formulas because it is so good an extracting useful information from a number. For example, in the worksheet below, the goal is to mark dates that occur in the same month and year with an "x". The TEXT provides an elegant way to do this in a simple formula.

For a detailed explanation of how this formula works, see this page. Here is a related example of the TEXT function used to sort birthdays while ignoring the year, useful when you want to see a list of birthdays in the coming year.

Notes

• The TEXT function always returns text.
• Value must be a numeric value.
• Format_text must appear in double quotation marks.
• The TEXT function can be used with custom number formats
• TEXT always returns text. To keep the number, use standard number formatting.