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.

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.

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"

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"

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"

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

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

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