Excel TEXT Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 84 of 105 · Full Excel course

The Excel TEXT function converts a number, date or time into a text string displayed in the format you specify, such as “dd-mmm-yyyy”, “#,##0.00” or “0%”. It returns text, so the value can be joined with other words in a sentence, used as a label or exported with a fixed appearance. TEXT is the function to reach for whenever a date or amount inside a formula shows up as a plain serial number.

Table of Contents

Syntax

=TEXT(value, format_text)

Arguments

Argument	Required / Optional	Meaning
value	Required	The number, date, time or cell reference to format.
format_text	Required	A number format code in double quotes, using the same codes as Format Cells > Custom, for example “0.00”, “dd/mm/yyyy” or “h:mm AM/PM”.

Useful format codes

The table below lists the format codes used most often with TEXT. Any code that works in the Custom number format dialog also works here.

Format Type	Details
0	Forces the display of a digit in its place
#	Display digit if it adds to the accuracy of the number (but don't display if a leading zero or a zero at the end of a decimal)
.	Defines the position that the decimal place takes
d	Day of the month or day of week
	d = one or two digit representation (e.g. 1, 12)
	dd = 2 digit representation (e.g. 01, 12)
	ddd = abbreviated day of week (e.g. Mon, Tue)
	dddd = full name of day of week (e.g. Monday, Tuesday)
m	Month (when used as part of a date)
	m = one or two digit representation (e.g. 1, 12)
	mm = two digit representation (e.g. 01, 12)
	mmm = abbreviated month name (e.g. Jan, Dec)
	mmmm = full name of month (e.g. January, December)
y	Year
	yy = 2-digit representation of year(e.g. 99, 08)
	yyyy = 4-digit representation of year(e.g. 1999, 2008)
h	Hour
	h = one or two digit representation (e.g. 1, 20)
	h = two digit representation (e.g. 01, 20)
m	Minute (when used as a part of a time)
	m = one or two digit representation (e.g. 1, 55)
	m = two digit representation (e.g. 01, 55)
s	Second
	s = one or two digit representation (e.g. 1, 45)
	ss = two digit representation (e.g. 01, 45)
AM/PM	Indicates that a time should be represented using a 12-hour clock, followed by "AM" or "PM"

Key points: 0 forces a digit, # shows a digit only if present, , adds a thousands separator, % multiplies by 100, and the letters d, m, y, h, s build date and time patterns. Because “m” means month, use “mm” inside a time only after “h” so Excel reads it as minutes.

Step-by-step example

The screenshot shows TEXT applied to the same value with different format codes.

Excel TEXT function example converting a date and a number into formatted text strings — Excel TEXT function example

Enter a date in A2 (for example 4 September 2026) and a number such as 1234.5 in A3.
In B2 enter =TEXT(A2,"dd-mmm-yyyy"). The result is 04-Sep-2026.
In C2 enter =TEXT(A2,"dddd"). The result is Friday.
In B3 enter =TEXT(A3,"#,##0.00"). The result is 1,234.50.
In C3 enter =TEXT(A3,"$#,##0"). The result is $1,235. All results are text and align left.

Practical use cases

1. Dates inside a sentence

="Report generated on "&TEXT(TODAY(),"dd mmmm yyyy")

2. Leading zeros for codes

=TEXT(A2,"00000")

Turns 42 into 00042, keeping the zeros when the sheet is exported to CSV.

3. Percentages and currency in labels

="Margin: "&TEXT(B2,"0.0%")&" on "&TEXT(C2,"₹#,##0")

4. Month and year grouping key

=TEXT(A2,"yyyy-mm")

Creates a sortable key such as 2026-09 for PivotTables and SUMIFS.

5. Elapsed time over 24 hours

=TEXT(B2-A2,"[h]:mm")

The square brackets stop hours rolling over at 24.

Common mistakes and tips

Result cannot be summed: TEXT returns text. Keep the raw values numeric and apply TEXT only for display or concatenation.
Locale differences: format codes follow the Excel display language. On a German system, “dd.mm.jjjj” is needed instead of “dd.mm.yyyy”.
#VALUE! error: the format code contains an unsupported character, or the value is text that cannot be interpreted.
Minutes vs months: “mm” alone means month. Write “h:mm” or “mm:ss” so Excel treats it as minutes.
Just changing appearance? Use Format Cells instead; TEXT is for when the formatted value must become part of a text string.

TEXT compared with number formatting, DOLLAR and FIXED

Cell formatting changes how a number looks while leaving it numeric, which is what you want for tables, charts and totals. TEXT produces a text copy in a chosen format, which is what you want inside sentences, labels, lookup keys and exports. DOLLAR and FIXED are special cases of TEXT with preset formats: DOLLAR(A2) equals TEXT(A2,”$#,##0.00″) on a US system and FIXED(A2,2) equals TEXT(A2,”#,##0.00″). TEXT is more flexible because it accepts any custom code, including conditional sections such as "[Green]0;[Red]-0" and text literals in quotes. To reverse the operation and turn formatted text back into a number, use VALUE or DATEVALUE.