Text Updated November 2026

TEXT

TEXT(value, format_text) converts a number or date to text in the format you specify, such as TEXT(A2, "dd/mm/yyyy") or TEXT(1234.5, "#,##0.00"). Use it when a number must appear inside a text string or keep a specific display; the result is text, so it cannot be summed.

Syntax

TEXT(value, format_text)

Part	What it is
`value`	The number, date or cell to format.
`format_text`	A format code in quotes, the same codes as Format Cells: "0.00", "#,##0", "0%", "yyyy-mm-dd", "mmm d", "000".

Examples

=TEXT(DATE(2026, 11, 3), "dd/mm/yyyy")

Result: 03/11/2026

"mmm d, yyyy" gives Nov 3, 2026 and "dddd" gives Tuesday.

=TEXT(1234.5, "#,##0.00")

Result: 1,234.50

=TEXT(0.256, "0.0%")

Result: 25.6%

="Invoice due " & TEXT(B2, "d mmmm yyyy")

Result: Invoice due 3 November 2026 when B2 is that date

Without TEXT the date shows as a serial number like 46329.

Common mistakes

The result cannot be summed: TEXT returns text. Keep the number in its own cell and use TEXT only for display or joining.
Format codes are language dependent: in a German or Dutch Excel the day code is T or d respectively, and the decimal separator differs. Use the codes your Excel uses in Format Cells.
TEXT(A2, "MM") returns the month, not minutes; minutes are only recognized next to h or s, as in "h:mm".
Leading zeros disappear when you convert back with VALUE; keep the TEXT version for IDs.

Practice TEXT

These drills check your answer on live data, free, no account needed.

Year and monthSplit the order date in A2 into its year (B2) and its month number (C2).

Questions

How do I convert a date to text in Excel?

=TEXT(A2, "yyyy-mm-dd") or any other date code. To go the other way, DATEVALUE turns date text back into a real date.

How do I add leading zeros in Excel?

=TEXT(A2, "00000") pads 42 to 00042. For display only, use the custom number format 00000 instead.

How do I show a number as currency in a sentence?

="Total: " & TEXT(B2, "$#,##0.00") gives Total: $1,234.50.

CONCAT and &How to join text in Excel with CONCAT, CONCATENATE and the & operator, with examples for full names, email addresses, numbered IDs and a formatted date.
DATE and DATEDIFHow the Excel DATE function builds a date from year, month and day, and DATEDIF counts complete years, months or days between two dates, plus an age formula.
ROUNDHow to round in Excel with ROUND, ROUNDUP, ROUNDDOWN and MROUND: to 2 decimals, to the nearest hundred and nearest 5, and why formatting is not rounding.
SUBSTITUTEHow the Excel SUBSTITUTE function replaces one piece of text with another, with examples for removing dashes, swapping the nth occurrence and counting commas.