The TEXT function in Excel
TEXT formats a value inside a formula: two decimals, a thousands separator, a percentage, or a date in the layout you want. The result is text, which is exactly what you need when you join a number or a date into a sentence.
Without it, ="Total: "&B2 shows the raw value: 12500 instead of 12,500, or 46099 instead of a date.
Syntax
=TEXT(value, format_text)
| Argument | What it means |
|---|---|
| value | The number, date or cell to format. |
| format_text | A format code in quotes, e.g. "0.00", "#,##0", "0%" or "yyyy-mm-dd". The same codes as in Format Cells › Custom. |
Examples
1. Two decimal places
| A | B | |
|---|---|---|
| 1 | Price | 3.5 |
| 2 | As text | 3.50 |
The format code "0.00" always shows two decimals.
2. A number in a sentence
| A | B | |
|---|---|---|
| 1 | Revenue | 12500 |
| 2 | Label | Total: 12,500 |
"#,##0" adds the thousands separator, so the label reads naturally.
3. A date in a fixed layout
| A | B | |
|---|---|---|
| 1 | Due | 2026-03-18 |
| 2 | As text | 2026-03-18 |
The date is stored as a number; TEXT writes it out as year-month-day, ready to join into a file name or message.
4. A percentage
| A | B | |
|---|---|---|
| 1 | Share | 0.256 |
| 2 | As text | 26% |
"0%" multiplies by 100, rounds to a whole number and adds the % sign.
Common mistakes
Calculating with the result. TEXT returns text, so the result is no longer a number for SUM.
Fix: Keep the original number for calculations and use TEXT only for display and labels.
Format codes depend on the language. In some Excel languages the date codes differ, e.g. "JJJJ" instead of "yyyy" in German.
Fix: Use the codes of your Excel's language, or use cell formatting where the layout doesn't need to be in the formula.
Frequently asked questions
How do I show the day of the week?
Use "dddd" for the full name or "ddd" for the short one: =TEXT(B1,"dddd").
Is TEXT the same as changing the cell format?
No. Cell formatting changes only how a value looks and keeps it a number. TEXT creates a new text value.