The TEXT function in Excel

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

Use =TEXT(A2,"dddd") for the full name, like Friday, or =TEXT(A2,"ddd") for Fri. For a number instead of a name, use WEEKDAY.

Q: How do I turn TEXT's result back into a number?

Use VALUE, which reads text like "$1,234.50" and returns 1234.5. Better still, keep the original number in its own cell and only format a copy.

TEXT turns a number or date into text with the format you choose, like $1,234.50 or Friday, March 14, 2025. You need it whenever you join a number to words with &, because joining throws the cell's formatting away. Use it to build report titles, receipt lines and month labels.

Syntax

=TEXT(value, format_text)

value	The number or date to format, usually a cell like `B2`.
format_text	A format code in double quotes, like `"$#,##0.00"`, `"0%"` or `"mmm d, yyyy"`. It uses the same codes as Format Cells.

Try it yourself

This is a live spreadsheet. Type the formula and press Enter.

In D2, use TEXT to turn the order date in B2 into a short month and year label, like Jan 2025. Reports often group orders by a label like this.

	A	B	C	D
1	Order	Order date	Amount	Month
2	SO-7712	3/14/2025	480
3	SO-7713	3/29/2025	1250
4	SO-7720	4/2/2025	315
5	SO-7731	5/18/2025	960
6
7
8

Tip: The format code goes in double quotes. Three m's give the short month name, and four y's give the full year.

Examples

=TEXT(B2,"$#,##0.00")→ "$1,234.50"

Shows the amount with a dollar sign, a thousands separator and two decimals. 0 is a digit that always shows, # one that shows only when needed.

=TEXT(C2,"dddd, mmmm d, yyyy")→ "Friday, March 14, 2025"

Spells out the date. dddd is the full weekday name and mmmm the full month name. Use ddd and mmm for the short versions.

="Tax rate: "&TEXT(D2,"0.0%")→ "Tax rate: 7.5%"

Joining D2 straight onto the words would show 0.075. TEXT keeps it looking like a percentage with one decimal.

Example data

1	Invoice	Amount	Date	Tax rate
2	INV-1041	1234.5	3/14/2025	7.50%

Common mistakes

Joining a date without TEXT
="Due "&C2 shows a number like 45730, because Excel stores dates as numbers. Wrap the date: ="Due "&TEXT(C2,"mmm d, yyyy").
Format code without quotes
TEXT(C2,mmm yyyy) returns #NAME?. The format code is text, so it always goes in double quotes: "mmm yyyy".
Doing math with the result
TEXT returns text, so SUM skips it and sorting goes A to Z instead of by size. Keep the original number for calculations and use TEXT only for labels.
Codes that change with the language
Date codes follow your Excel's language. A workbook built with yyyy can show odd results in a version that uses another letter for year. If a file is shared across regions, prefer Format Cells where you can.

FAQ

What's the difference between TEXT and Format Cells?+

Format Cells (Ctrl+1) only changes how a number looks, and the cell stays a number you can calculate with. TEXT creates new text, which you need when the number goes inside a sentence or label.

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

Use =TEXT(A2,"dddd") for the full name, like Friday, or =TEXT(A2,"ddd") for Fri. For a number instead of a name, use WEEKDAY.

How do I turn TEXT's result back into a number?+

Use VALUE, which reads text like "$1,234.50" and returns 1234.5. Better still, keep the original number in its own cell and only format a copy.

Lesson

Master TEXT step by step: TEXT and VALUE: numbers to text and back

Format dates and dollar amounts inside sentences with TEXT, and turn digits stored as text into real numbers with VALUE.

Related functions

VALUE TEXTJOIN DATEDIF