TEXT Function

Converts a number or date into formatted text using a format code.

PUBLISHEDApr 06, 2026

Summary

The Excel TEXT function converts a number or date into a text string using a format code. For example, =TEXT(1250.5,"$#,##0.00") returns "$1,250.50".

TEXT is useful when a value needs to appear in a specific format inside a label, sentence, export field, or helper column. It gives you more control than ordinary cell formatting because the formatted result becomes part of the formula output itself.

TEXT becomes especially important when values are still numeric underneath but must appear in a very specific display format inside another formula. Dates, percentages, times, and currency often look right on the sheet because of cell formatting, but once you join them into text, that formatting disappears unless TEXT rebuilds it explicitly.

That is why TEXT is both powerful and easy to misuse. It solves presentation problems cleanly, but the result is text, not a number you can keep calculating with. It works best at the final display stage, when the workbook needs a formatted label, message, or export string rather than another numeric input.

Purpose

Format a value as text

Applies a custom number or date format and returns the result as text.

Return Value

A formatted text string

Always returns text, even when the source value is numeric or a date.

Syntax

=TEXT(value, format_text)

value is the number or date you want to format. format_text is the format code in quotation marks, such as "$#,##0.00", "00000", or "yyyy-mm-dd".

Arguments

value - [Required] The number, date, or cell reference you want to format.
format_text - [Required] The Excel format code that controls how the value should appear.

TEXT vs Other Functions

TEXT is the most flexible display-formatting function in this group. It works well when simpler functions like FIXED or DOLLAR are not enough.

Function	Returns	Use When
`TEXT`	Formatted text	You need a specific custom format
`FIXED`	Formatted text	You only need decimal control and optional commas
`DOLLAR`	Formatted text	You want a currency-style result with a currency symbol
`ROUND`	Number	You need a rounded result that still stays numeric

Using TEXT

TEXT is often used when a number or date has to be inserted into a sentence. Without TEXT, a formula like ="Profit: "&B1 may show a raw value instead of the display style you want. Wrapping the number in TEXT keeps the separators, decimals, symbols, or date pattern under your control.

TEXT is also useful for display-only IDs. A format code like "00000" can show leading zeros without changing the stored number. That is useful when the workbook still needs the original value for calculations or sorting.

Use TEXT when the formula result must include a specific display format.
Use zero-based codes like "00000" for fixed-width ID display.
Keep the original number or date for calculations, because TEXT returns text.

Example 1 - Formatting a Number as Currency

The format code controls the currency symbol, decimal places, and separators. The result looks like a currency value, but it is text.

=TEXT(A1, "$#,##0.00")
// 12.5 -> "$12.50"

=TEXT(B1, "$#,###")
// 5000 -> "$5,000"

Check Answer

Challenge #1

Target: Sheet1!B1

In cell B1, use TEXT on A1 with a currency format pattern.

Example 2 - Adding Leading Zeros to an ID

A zero-based format code forces a minimum digit width. If the number is shorter than that width, TEXT pads it with leading zeros in the returned text string.

=TEXT(B2, "00000")
// 123 -> "00123"
// 5   -> "00005"

Check Answer

Challenge #2

Target: Sheet1!B2

In cell B2, use TEXT on A2 with a zero-padded format pattern.

Example 3 - Formatting a Date as Text

TEXT can return only the part of the date you want to show. That makes it useful in headings, labels, exported text, and date-based summaries.

=TEXT(C1, "yyyy")       // "2026"
=TEXT(C1, "dd/MM/yyyy") // "04/04/2026"
=TEXT(C1, "MMMM yyyy")  // "April 2026"

Check Answer

Challenge #3

Target: Sheet1!B3

In cell B3, use TEXT on C1 with a date format pattern.

Example 4 - Embedding a Formatted Number in a Sentence

This is one of the most common uses of TEXT. It lets a formula produce a finished sentence while keeping the number in a clear display format.

="Profit: "&TEXT(B1, "$#,###")   // "Profit: $5,000"
="Rate: "&TEXT(0.15, "0%")       // "Rate: 15%"
="As of: "&TEXT(C1, "dd MMM yyyy")

Check Answer

Challenge #4

Target: Sheet1!B4

In cell B4, combine a label with TEXT formatting of A4.

Be careful with date and time format codes. In date formats, month and minute codes can look similar, so it helps to test the result and confirm the output matches what you intended.

Conclusion Recap

TEXT is the function to use when a number or date needs to look a certain way inside a formula result. In this lesson, that included currency, leading zeros, date parts, and full sentences with formatted values inside them.

The important tradeoff is that the final result becomes text. That is great for display, labels, and exports, but the original number or date should stay somewhere else if you still need calculations.

Summary: TEXT formats a value and returns the result as text.
Syntax: =TEXT(value, format_text).
Key point: The result is text, so it should usually be used at the final display step.
Practical usage: Currency labels, date labels, leading zeros, and formatted text output.
Best pattern: Use TEXT inside concatenation when the displayed format matters.

Tactical Arena

Share TEXT Function!

Tell your friends about this post

Discussion