The Excel TEXT function converts a number or date into text in exactly the format you specify — $1,834.50, 06/19/2026, Friday, 07501. It is the bridge between Excel’s raw values and human-readable labels, and the only way to control formatting inside a concatenated sentence. The trade-off: the result is text, not a number, so don’t do math on it afterwards.
Invoice due 46184 — the date’s raw serial number. The second argument is a format code in quotes, the same codes used in Format Cells › Custom.
Syntax
| Argument | Description | |
|---|---|---|
value | Required | The number, date, or time to convert — a cell reference, a literal number, or another formula’s result. |
format_text | Required | A format code in quotes, e.g. "0.00" or "mm/dd/yyyy". These are the same codes as Format Cells › Number › Custom. |
Available in: every version of Excel, Excel for the web, and Google Sheets. Rule of thumb: cell formatting changes how a number looks while keeping it a number; TEXT creates a new text string. Use cell formatting when you can, TEXT when the value must live inside a sentence or stay text.
Format code reference table
The format_text argument is a mini-language. 0 means “always show a digit here (pad with zero)”, # means “show a digit only if there is one”, and letters like m, d, y, h pull pieces out of dates and times. The codes you’ll actually use:
| Format code | Input | TEXT returns | Use for |
|---|---|---|---|
"0.00" | 3.14159 | 3.14 | Fixed decimal places |
"#,##0" | 1834.5 | 1,835 | Thousands separator, no decimals |
"$#,##0.00" | 1834.5 | $1,834.50 | Currency |
"0%" | 0.082 | 8% | Percentages (input is the decimal) |
"0.0%" | 0.082 | 8.2% | Percentages with a decimal |
"00000" | 7501 | 07501 | ZIP codes & IDs with leading zeros |
"mm/dd/yyyy" | 6/19/2026 | 06/19/2026 | Dates, US style |
"dddd" | 6/19/2026 | Friday | Weekday name (use "ddd" for Fri) |
"mmm d, yyyy" | 6/19/2026 | Jun 19, 2026 | Readable dates |
"[h]:mm" | 1.5 | 36:00 | Elapsed hours beyond 24 |
"0.0,," | 2400000 | 2.4 | Show millions (each comma drops 3 zeros) |
"+0;-0;0" | 5 | +5 | Forced sign (positive;negative;zero) |
Don’t memorize — steal. Format a cell the way you want via Format Cells › Custom, copy the code from the Type box, and paste it into TEXT. Any custom number format works as a TEXT format code.
Worked example: numbers and dates inside sentences
Concatenating a date or currency value straight into text exposes the raw value. TEXT fixes both rows — here B2 holds the date 6/19/2026 and C2 holds 1834.5:
| A | B | |
|---|---|---|
| 1 | Formula | Result |
| 2 | ="Due " & B2 | Due 46184 ← raw serial |
| 3 | ="Due " & TEXT(B2,"mm/dd/yyyy") | Due 06/19/2026 |
| 4 | ="Total: " & C2 | Total: 1834.5 ← unformatted |
| 5 | ="Total: " & TEXT(C2,"$#,##0.00") | Total: $1,834.50 |
Two more everyday wins — leading zeros that Excel normally strips, and weekday names for report headers:
Try it: interactive TEXT demo
Enter a number and pick a format code — the formula and its text result update live.
Errors & common pitfalls
The big one: TEXT returns text, and text breaks math. =TEXT(C2,"$#,##0.00") looks like a number but SUM, AVERAGE, and charts treat it as zero-width air. Keep the real number in its own cell with cell formatting; use TEXT only for labels, sentences, and exports. If you must convert back, wrap in VALUE or NUMBERVALUE.
#VALUE! — bad format code. Misspell a code ("mn/dd/yyyy") or forget the quotes around format_text and TEXT fails. Format codes must be quoted strings.
Pitfall: minutes vs months. m means month — unless it sits right after h or right before ss, where it means minutes. "h:mm" is hours:minutes; "m/d" is month/day. If your “minutes” look like months, that’s why.
Pitfall: TEXT rounds for display. =TEXT(3.14159,"0.00") returns the string 3.14 — the extra precision is gone from the result (the source cell is untouched). Cell formatting, by contrast, keeps full precision underneath.
Pitfall: format codes follow your regional settings. A workbook built with "mm/dd/yyyy" opened in a locale that writes day-first still shows the US layout — and some locales use different code letters entirely (e.g. jj for day in French Excel). Test before you ship internationally.
Practice workbook
Frequently asked questions
What's the difference between TEXT and just formatting the cell?
How do I keep leading zeros in ZIP codes or IDs?
=TEXT(A2,"00000") pads to five digits: 7501 becomes 07501. For data you type rather than compute, formatting the column as Text (or a custom 00000 cell format) before entry also works.Why does my date show as a number like 46184 when I concatenate it?
="Due " & TEXT(B2,"mm/dd/yyyy") returns Due 06/19/2026.Why won't SUM add up my TEXT results?
How do I show hours over 24, like total time worked?
=TEXT(A2,"[h]:mm") shows 1.5 (days) as 36:00. Without the brackets, hours roll over at 24 like a clock and you lose whole days.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class