TEXT function in Excel

TEXT turns a number or date into text in the format you choose.

A cell's number format changes how a value looks while keeping it a number. TEXT does something different: it produces a piece of text that looks the way you want. You need it whenever a number or date goes inside a sentence, a label or a joined code, because joining strips the formatting away.

Syntax

=TEXT(value, format_text)

Argument	What it is
`value`	The number or date to format.
`format_text`	A format code in quotation marks, such as "0.00", "#,##0" or "dd-mmm-yyyy".

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Twelve sales made by four salespeople in three regions.

	A	B	C	D	E	F
1	Date	Salesperson	Region	Product	Units	Amount
2	05-Jan-2026	Asha	North	Notebook	20	1200
3	12-Jan-2026	Ravi	South	Pen	50	500
4	20-Jan-2026	Meena	North	Bag	4	3000
5	02-Feb-2026	Asha	East	Pen	30	300
6	14-Feb-2026	Kabir	South	Notebook	15	900
7	28-Feb-2026	Ravi	North	Bag	2	1500
8	03-Mar-2026	Meena	East	Notebook	40	2400
9	15-Mar-2026	Asha	North	Bag	3	2250
10	22-Mar-2026	Kabir	South	Pen	60	600
11	01-Apr-2026	Ravi	East	Notebook	25	1500
12	18-Apr-2026	Meena	North	Pen	80	800
13	06-May-2026	Asha	South	Bag	5	3750

=TEXT(A2,"dd-mmm-yyyy")
Result: 05-Jan-2026
A date shown with a three-letter month.
=TEXT(A2,"dddd")
Result: Monday
The full name of the day of the week.
=TEXT(A2,"mmmm yyyy")
Result: January 2026
Month and year, the usual label for a monthly report.
=TEXT(F4,"#,##0.00")
Result: 3,000.00
A number with a thousands separator and two decimals.
=TEXT(F4/SUM(F2:F13),"0.0%")
Result: 16.0%
A share of the total shown as a percentage with one decimal.
=TEXT(E9,"000")
Result: 003
Leading zeros: 3 becomes 003.
="Sale on "&TEXT(A2,"d mmm")&": Rs "&TEXT(F2,"#,##0")
Result: Sale on 5 Jan: Rs 1,200
The reason TEXT exists: a date and an amount inside a sentence.
=TEXT(A2,"yyyy-mm")
Result: 2026-01
A year-and-month key for grouping, such as 2026-01.

How it works

TEXT takes a value and a format code and returns the value written out in that format. The codes are the same ones used in Format Cells, under Custom, so any format you can build there can be given to TEXT.

Format codes for numbers

Code	1234.5 becomes	Meaning
`"0"`	1235	whole number
`"0.00"`	1234.50	two decimals, always shown
`"#,##0"`	1,235	thousands separator
`"#,##0.00"`	1,234.50	separator and two decimals
`"0.0%"`	123450.0%	percentage (the value is multiplied by 100)
`"00000"`	01235	padded with zeros to five digits

0 shows a digit always, with a zero if there is none. # shows a digit only when there is one.

The comma separator follows the international pattern, in groups of three. For the Indian pattern of lakhs and crores, a custom format such as "[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0" is commonly used.

Format codes for dates

Code	Gives	Example
`d`	day	5
`dd`	day, two digits	05
`ddd`	short day name	Mon
`dddd`	full day name	Monday
`m`	month number	1
`mm`	month, two digits	01
`mmm`	short month name	Jan
`mmmm`	full month name	January
`yy`	year, two digits	26
`yyyy`	year, four digits	2026
`h:mm AM/PM`	time on a 12-hour clock	6:30 PM

Combine them freely with spaces, dashes and slashes: "dd/mm/yyyy", "d mmmm yyyy", "ddd, d mmm".

The result is text

This is the point to remember. The output of TEXT cannot be added, averaged or compared as a number. =TEXT(A1,"0.00")+1 happens to work because Excel converts it back, but SUM over a column of TEXT results gives 0, and sorting them sorts as text, where "100" comes before "20".

So:

to change how a number looks in its own cell, use the cell's number format;
to round a number, use ROUND;
to put a number or date into text, use TEXT.

Useful patterns

A sentence: ="As on "&TEXT(TODAY(),"d mmmm yyyy").
Month labels for a summary: =TEXT(A2,"mmm-yy") turns each date into its month, which can then be counted or used as a heading.
Fixed-width codes: ="INV-"&TEXT(B2,"0000") gives INV-0042.
Day names: =TEXT(A2,"dddd").
Comparing a date with a text month: =TEXT(A2,"mmm")="Mar".

Regional settings

Date codes depend on the language of the computer. On a system set to German, the year code is j, not y. A workbook that uses TEXT with date codes may show odd results on a computer with different regional settings. Number codes are more portable, though the decimal and thousands characters also follow the system.

Common mistakes

Forgetting the quotation marks around the code.
Using mm for minutes on its own. After h it means minutes. Elsewhere it means month.
Adding up TEXT results.
Writing the code in capitals. "DD-MM-YYYY" works, but "MM" and "mm" are the same, so capitals do not separate months from minutes.

In Google Sheets

TEXT works the same way in Google Sheets, with the same codes for the common cases.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.