Excel practice › Excel functions › TEXT

TEXT in Excel: format a number or a date as text

TEXT turns a number or a date into text, written the way a format code says. =TEXT(C2,"$#,##0.00") shows 18425.75 as $18,425.75, and =TEXT(B2,"mmmm d, yyyy") shows a date as March 7, 2026.

Its main use is inside a join, to keep a number or a date readable in a sentence. The result is text, not a number: it can't be added up, so keep the original value in its own cell.

Skip to the 3 exercises ↓

Syntax

=TEXT(value, format_text)

value
The number or the date to format: usually a cell.
format_text
The format code, in quotes: "0.00", "$#,##0", "mmm yyyy". Upper or lower case makes no difference: "MM/DD/YY" and "mm/dd/yy" are the same code.
Live exampleSales as money
F2fx
ABCDEF
1StoreWeek endingSalesvs planStore no.As text
2Austin3/7/202618425.750.1242$18,425.75
3Tucson3/7/20269310.25-0.047
4Omaha3/14/2026120880.08118
5Raleigh3/14/202615640.750.1563
6Spokane3/21/20267902.40.039

The format codes you'll use most

Each result below was produced by the formula =TEXT(value, code).

CodeValueResult
"0"18425.7518426
"0.0"18425.7518425.8
"#,##0"18425.7518,426
"$#,##0.00"18425.75$18,425.75
"0%"0.1212%
"0.0%"0.1212.0%
"00000"4200042
"m/d/yyyy"the date 3/7/20263/7/2026
"mm/dd/yy"the date 3/7/202603/07/26
"mmm d, yyyy"the date 3/7/2026Mar 7, 2026
"mmmm yyyy"the date 3/7/2026March 2026
"ddd"the date 3/7/2026Sat
"dddd"the date 3/7/2026Saturday
"yyyy-mm-dd"the date 3/7/20262026-03-07

In number codes, 0 always shows a digit (a zero when there is nothing else) and # shows a digit only when there is one. In date codes the count of letters sets the form: m is 3, mm is 03, mmm is Mar, mmmm is March. The day follows the same pattern: d is 7, dd is 07, and ddd and dddd give the weekday, Sat and Saturday.

Live exampleA date in words
F2fx
ABCDEF
1StoreWeek endingSalesvs planStore no.As text
2Austin3/7/202618425.750.1242March 7, 2026
3Tucson3/7/20269310.25-0.047
4Omaha3/14/2026120880.08118
5Raleigh3/14/202615640.750.1563
6Spokane3/21/20267902.40.039

Where it goes wrong: the result is text

Once a number has been through TEXT, it's a piece of text that looks like a number. In this sheet, column C was filled with =TEXT(B2,"$#,##0.00"), and then someone added up column C:

Live exampleSUM of formatted values: 0
C7fx
ABC
1StoreSalesShown as
2Austin18425.75$18,425.75
3Tucson9310.25$9,310.25
4Omaha12088$12,088.00
5Raleigh15640.75$15,640.75
6Spokane7902.4$7,902.40
7Total0

Microsoft's page gives the same advice: keep the original value in one cell and use TEXT in another. And when all you want is a different look for a number you'll still calculate with, don't use TEXT at all: change the cell's number format (Ctrl+1). The cell then shows $18,425.75 and still holds the number.

Practice: 3 exercises

Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in value won't pass.

Exercise 1The weekday of a date

In F2, return the name of the weekday of the date in B2, written in full (like Monday).

F2fx
ABCDEF
1StoreWeek endingSalesvs planStore no.Weekday
2Austin3/7/202618425.750.1242
3Tucson3/7/20269310.25-0.047
4Omaha3/14/2026120880.08118
5Raleigh3/14/202615640.750.1563
6Spokane3/21/20267902.40.039
Show the answer

=TEXT(B2,"dddd") gives Saturday.

dddd is the code for the full weekday name. The date in B2 stays a date; F2 holds the text Saturday.

Exercise 2Sales as money

In F3, show Tucson's sales (C3) as text in the form $9,310.25: dollar sign, thousands separator, two decimals.

F3fx
ABCDEF
1StoreWeek endingSalesvs planStore no.As text
2Austin3/7/202618425.750.1242
3Tucson3/7/20269310.25-0.047
4Omaha3/14/2026120880.08118
5Raleigh3/14/202615640.750.1563
6Spokane3/21/20267902.40.039
Show the answer

=TEXT(C3,"$#,##0.00") gives $9,310.25.

#,##0 writes the number with thousands separators, .00 forces two decimals (12088 becomes $12,088.00), and the $ is copied as it is.

Exercise 3A sentence with a percentage

For the weekly email: in F2, build Austin: 12% vs plan from the store name in A2 and the number in D2.

F2fx
ABCDEF
1StoreWeek endingSalesvs planStore no.Line
2Austin3/7/202618425.750.1242
3Tucson3/7/20269310.25-0.047
4Omaha3/14/2026120880.08118
5Raleigh3/14/202615640.750.1563
6Spokane3/21/20267902.40.039
Show the answer

=A2&": "&TEXT(D2,"0%")&" vs plan" gives Austin: 12% vs plan.

TEXT(D2,"0%") multiplies by 100 and adds the sign, giving the text 12%. Joined without it, the line would read Austin: 0.12 vs plan.

Questions people ask

Why can't I add up the results of TEXT?

Because TEXT returns text, and SUM ignores text in a range. Do the math on the original numbers and apply TEXT last, to the result: =TEXT(SUM(B2:B6),"$#,##0.00"). If you only want the cells to look different, change their number format (Ctrl+1) and they stay numbers.

How do I add leading zeros in Excel?

Use a code with as many zeros as the digits you want: =TEXT(E2,"00000") turns 42 into 00042. The result is text, which is what you want for codes like ZIP codes or store numbers that are never calculated with.

How do I get the month name or the weekday from a date?

With date codes: =TEXT(B2,"mmmm") returns the month (March), =TEXT(B2,"mmm") its short form (Mar), =TEXT(B2,"dddd") the weekday (Saturday) and =TEXT(B2,"ddd") its short form (Sat).

What is the difference between 0 and # in a format code?

Both stand for a digit. 0 shows a zero when the number has no digit in that place; # shows nothing. With 7.5 in A2, =TEXT(A2,"0.00") gives 7.50 and =TEXT(A2,"#.##") gives 7.5.

Keep going

Lesson 7 is seven drills on a design studio's invoices: days between two dates, month-end due dates with EOMONTH, business days with NETWORKDAYS, and TEXT for month labels and amounts inside a sentence. It's part of Pro ($29 once for all three tracks, or $19 for the Excel track alone). Lessons 1 and 2 and the skills test are free.

Related

CONCAT function and the & operator, with live examplesEOMONTH function, with live examplesDATEDIF function, with live examplesLEFT, RIGHT and MID functions, with live examples#VALUE! error in Excel: meaning and fixesExcel date functions practice (Pro)Excel functions with live examplesExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: TEXT function, Format a date the way you want, Review guidelines for customizing a number format. Last checked November 2026.