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.
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.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Store | Week ending | Sales | vs plan | Store no. | As text |
| 2 | Austin | 3/7/2026 | 18425.75 | 0.12 | 42 | $18,425.75 |
| 3 | Tucson | 3/7/2026 | 9310.25 | -0.04 | 7 | |
| 4 | Omaha | 3/14/2026 | 12088 | 0.08 | 118 | |
| 5 | Raleigh | 3/14/2026 | 15640.75 | 0.15 | 63 | |
| 6 | Spokane | 3/21/2026 | 7902.4 | 0.03 | 9 |
# signs adds thousands separators, and .00 forces two decimals. Try "$#,##0" for whole dollars, or point at D2 with the code "0%".The format codes you'll use most
Each result below was produced by the formula =TEXT(value, code).
| Code | Value | Result |
|---|---|---|
"0" | 18425.75 | 18426 |
"0.0" | 18425.75 | 18425.8 |
"#,##0" | 18425.75 | 18,426 |
"$#,##0.00" | 18425.75 | $18,425.75 |
"0%" | 0.12 | 12% |
"0.0%" | 0.12 | 12.0% |
"00000" | 42 | 00042 |
"m/d/yyyy" | the date 3/7/2026 | 3/7/2026 |
"mm/dd/yy" | the date 3/7/2026 | 03/07/26 |
"mmm d, yyyy" | the date 3/7/2026 | Mar 7, 2026 |
"mmmm yyyy" | the date 3/7/2026 | March 2026 |
"ddd" | the date 3/7/2026 | Sat |
"dddd" | the date 3/7/2026 | Saturday |
"yyyy-mm-dd" | the date 3/7/2026 | 2026-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.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Store | Week ending | Sales | vs plan | Store no. | As text |
| 2 | Austin | 3/7/2026 | 18425.75 | 0.12 | 42 | March 7, 2026 |
| 3 | Tucson | 3/7/2026 | 9310.25 | -0.04 | 7 | |
| 4 | Omaha | 3/14/2026 | 12088 | 0.08 | 118 | |
| 5 | Raleigh | 3/14/2026 | 15640.75 | 0.15 | 63 | |
| 6 | Spokane | 3/21/2026 | 7902.4 | 0.03 | 9 |
"dddd" for the weekday (Saturday), or to "mmm yyyy" for a month label (Mar 2026).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:
| A | B | C | |
|---|---|---|---|
| 1 | Store | Sales | Shown as |
| 2 | Austin | 18425.75 | $18,425.75 |
| 3 | Tucson | 9310.25 | $9,310.25 |
| 4 | Omaha | 12088 | $12,088.00 |
| 5 | Raleigh | 15640.75 | $15,640.75 |
| 6 | Spokane | 7902.4 | $7,902.40 |
| 7 | Total | 0 |
=SUM(B2:B6) gives 63367.15, and =TEXT(SUM(B2:B6),"$#,##0.00") shows it as $63,367.15.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.
In F2, return the name of the weekday of the date in B2, written in full (like Monday).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Store | Week ending | Sales | vs plan | Store no. | Weekday |
| 2 | Austin | 3/7/2026 | 18425.75 | 0.12 | 42 | |
| 3 | Tucson | 3/7/2026 | 9310.25 | -0.04 | 7 | |
| 4 | Omaha | 3/14/2026 | 12088 | 0.08 | 118 | |
| 5 | Raleigh | 3/14/2026 | 15640.75 | 0.15 | 63 | |
| 6 | Spokane | 3/21/2026 | 7902.4 | 0.03 | 9 |
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.
In F3, show Tucson's sales (C3) as text in the form $9,310.25: dollar sign, thousands separator, two decimals.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Store | Week ending | Sales | vs plan | Store no. | As text |
| 2 | Austin | 3/7/2026 | 18425.75 | 0.12 | 42 | |
| 3 | Tucson | 3/7/2026 | 9310.25 | -0.04 | 7 | |
| 4 | Omaha | 3/14/2026 | 12088 | 0.08 | 118 | |
| 5 | Raleigh | 3/14/2026 | 15640.75 | 0.15 | 63 | |
| 6 | Spokane | 3/21/2026 | 7902.4 | 0.03 | 9 |
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.
For the weekly email: in F2, build Austin: 12% vs plan from the store name in A2 and the number in D2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Store | Week ending | Sales | vs plan | Store no. | Line |
| 2 | Austin | 3/7/2026 | 18425.75 | 0.12 | 42 | |
| 3 | Tucson | 3/7/2026 | 9310.25 | -0.04 | 7 | |
| 4 | Omaha | 3/14/2026 | 12088 | 0.08 | 118 | |
| 5 | Raleigh | 3/14/2026 | 15640.75 | 0.15 | 63 | |
| 6 | Spokane | 3/21/2026 | 7902.4 | 0.03 | 9 |
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.