TEXT function in Google Sheets

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

When a number or a date is joined into a sentence, it loses its formatting. A date turns into a five-digit number and an amount loses its commas. TEXT puts the format back by turning the value into text that looks the way you want.

Syntax

=TEXT(number, format)

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

Examples

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 with a short month name.
=TEXT(A2,"dddd")
Result: Monday
The name of the day.
=TEXT(A2,"mmmm yyyy")
Result: January 2026
Month and year.
=TEXT(F4,"#,##0.00")
Result: 3,000.00
A number with a separator and two decimals.
=TEXT(F4/SUM(F2:F13),"0.0%")
Result: 16.0%
A share shown as a percentage.
=TEXT(E9,"000")
Result: 003
Padded with leading zeros.
="Sale on "&TEXT(A2,"d mmm")&": Rs "&TEXT(F2,"#,##0")
Result: Sale on 5 Jan: Rs 1,200
A date and an amount inside a sentence.

How it works

TEXT takes a value and a pattern. The patterns are those of Format, Number, Custom number format.

Number patterns

Pattern	1234.5 becomes
`"0"`	1235
`"0.00"`	1234.50
`"#,##0"`	1,235
`"#,##0.00"`	1,234.50
`"0%"`	123450%
`"00000"`	01235

0 always shows a digit. # shows one only when it exists.

Date and time patterns

Pattern	Gives
`d`, `dd`	day: 5, 05
`ddd`, `dddd`	Mon, Monday
`m`, `mm`	month number: 1, 01
`mmm`, `mmmm`	Jan, January
`yy`, `yyyy`	26, 2026
`hh:mm`	hours and minutes
`hh:mm am/pm`	on a 12-hour clock

The result is text

TEXT output cannot be added up, and it sorts as text. So:

to change how a value looks in its own cell, use Format, Number;
to round a value, use ROUND;
to put a value into text, use TEXT.

Where Sheets users need it

Dates inside QUERY. A QUERY condition wants a date written as date 'yyyy-mm-dd'. With the date in H1:

"where A >= date '"&TEXT(H1, "yyyy-mm-dd")&"'"

Grouping by month. =TEXT(A2, "yyyy-mm") gives a key such as 2026-01 that sorts correctly and keeps the years apart.

Labels and messages. ="Report for "&TEXT(TODAY(), "mmmm yyyy").

Fixed-width codes. ="INV-"&TEXT(B2, "0000").

A whole column. In ARRAYFORMULA: =ARRAYFORMULA(IF(A2:A="", "", TEXT(A2:A, "ddd d mmm"))).

Locale

Month and day names appear in the language of the spreadsheet's locale, set under File, Settings. The same formula shows "January" in an English locale and the local word in another. The decimal and thousands marks follow the locale too.

Sheets has a related function, TO_TEXT, which converts a value to text using whatever format the cell already has.

Common mistakes

The pattern without quotation marks.
Adding up TEXT results, which gives 0.
Using mm for minutes without an hour code beside it. On its own it means month.
Expecting the Indian digit grouping from #,##0. It groups in threes. A custom number format is needed for lakhs and crores.

In Excel

TEXT is the same in Excel, with the same common patterns.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

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