How do I format a percentage with TEXT?

Use =TEXT(0.256,"0.0%"), which gives 25.6%.

How do I get the day of the week from a date?

Use =TEXT(A2,"dddd") for the full name or "ddd" for the short name.

Why does TEXT return a number-looking result that does not add up?

Because it is text. Wrap it in VALUE or sum the original cells.

What is the difference between TEXT and cell formatting?

Cell formatting changes only how a number looks. TEXT creates a new text value you can join into sentences.

← All functions

Text

TEXT

Number or date as text in a given format.

Syntax

TEXT(value, format_text)

Example: =TEXT(B2,"$#,##0")

Available in: All versions

TEXT converts a number or date into text formatted the way you specify: =TEXT(value, format_text). Use it to build labels such as "Total: $1,234.50" or "Mar 9, 2024", or to pad numbers with leading zeros.

When to use it

You join a number or date into a sentence with & and need it formatted, not raw.
You need leading zeros on codes such as ZIP codes or invoice numbers.
You want a month name, weekday or year from a date.
You build chart titles or report headers that update with data.

Arguments explained

value. The number, date or reference to convert. Text that cannot be read as a number or date is returned unchanged or causes #VALUE!.
format_text. The pattern in quotes. Digit codes are 0 (required digit), # (optional digit), comma (thousands), . (decimal point) and % (percent). Date codes are d, dd, ddd, dddd, m, mmm, mmmm, yy and yyyy, with h, mm and ss for time.
Locale. The letters used in format_text depend on your regional settings, so a workbook shared across countries can display differently.

Worked examples

Currency with thousands separator

	A
1	Amount
2	1234.5

Formula: C1: =TEXT(A2,"$#,##0.00")

Result: $1,234.50

#,##0 adds the thousands separator, 0.00 forces two decimals.

Readable date

	A
1	Date
2	3/9/2024

Formula: C1: =TEXT(A2,"mmm d, yyyy")

Result: Mar 9, 2024

mmm is the short month name, d the day, yyyy the four-digit year. Use "dddd" for the weekday name.

Leading zeros

	A
1	Invoice
2	42

Formula: C1: =TEXT(A2,"00000")

Result: 00042

Each 0 is a mandatory digit, so 42 is padded to five characters.

Try it

A tiny spreadsheet with example data. Edit the formula or any number and see the result.

Formula in D2: =TEXT(A2,"#,##0.00")

Example spreadsheet for TEXT
	A	B	C	D
1	Number	Step		Result
2	1234.567	50		1,234.57
3	17	5
4	-4.7	12
5	144	9
6	2.5	3
7	50	12

Result in D2: 1,234.57

Practice this in a lesson: The TEXT and VALUE Functions

Common errors and how to fix them

Result cannot be summed. TEXT returns text. Keep the original number for calculations and use TEXT only for display.
Format code ignored. The format must be in quotes. Month versus minute is ambiguous: "mm" after "h" means minutes, otherwise months.
Different result on another computer. Format codes are locale-dependent. In some regional settings the decimal and thousands separators or date letters differ.
#VALUE!. The value is text that Excel cannot read as a number or date.

Related functions

Frequently asked questions

How do I format a percentage with TEXT?: Use =TEXT(0.256,"0.0%"), which gives 25.6%.
How do I get the day of the week from a date?: Use =TEXT(A2,"dddd") for the full name or "ddd" for the short name.
Why does TEXT return a number-looking result that does not add up?: Because it is text. Wrap it in VALUE or sum the original cells.
What is the difference between TEXT and cell formatting?: Cell formatting changes only how a number looks. TEXT creates a new text value you can join into sentences.