# TEXT function in Excel

> TEXT turns a number or date into text in the format you choose, such as "$1,250.00" or "March 2026".

Source: https://spreadsheetpractice.com/functions/text · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** TEXT turns a number or date into text in the format you choose, such as "$1,250.00" or "March 2026".
- **Syntax:** `=TEXT(value, format_text)`
- **Most common use:** `="Total: "&TEXT(B2,"$#,##0.00")` returns Total: $1,234.50 (Keeps the currency format inside a sentence)
- **Available in:** Every current version
- **Category:** [Text functions](https://spreadsheetpractice.com/functions/category/text)
- **Returns:** Text that looks like the formatted value. It can't be summed or used in date math.
- **Google Sheets:** Available in Google Sheets with the same arguments.

## What it does

TEXT turns a number or date into text in the format you choose, such as "$1,250.00" or "March 2026". You need it whenever you join numbers or dates with words, because joining drops the cell's formatting and shows the raw stored value.

## Syntax

`=TEXT(value, format_text)`

| Argument | Required? | What it is |
|---|---|---|
| `value` | Required | The number, date or cell to format. |
| `format_text` | Required | A format code in quotes, using the same codes as Format Cells: "$#,##0.00", "0.0%", "mmm d, yyyy", "dddd". |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | `="Total: "&TEXT(B2,"$#,##0.00")` | Total: $1,234.50 | Keeps the currency format inside a sentence |
| D3 | `="Due "&TEXT(A3,"mmm d, yyyy")` | Due Nov 30, 2026 | A readable date; without TEXT it would show 46356 |
| D4 | `=TEXT(A4,"dddd")` | Saturday | The day of the week as a word |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D |
|---|---|---|---|---|
| 1 | Order date | Total | Margin | Result |
| 2 | 3/7/2026 | $1,234.50 | 25.6% | Total: $1,234.50 |
| 3 | 11/30/2026 | $89.00 | 31.0% | Due Nov 30, 2026 |
| 4 | 7/4/2026 | $15,000.00 | 8.2% | Saturday |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| The results won't add up | TEXT returns text, so SUM ignores it. Keep the number in its own cell for math and use TEXT only for display. |
| The format code gives the wrong result | Codes go in quotes, and m means month in a date but minutes after h. Copy the code from Format Cells > Custom to be sure it's right. |
| Codes break in another language version | Format codes follow the language of Excel, so "yyyy" may not work for a colleague using, for example, German Excel. Keep shared reports to formatted cells where you can. |

## Tips

- Pad numbers with leading zeros using a code like "00000", for ZIP codes and IDs.
- For labels, "mmm d" gives Mar 7 and "dddd, mmmm d" gives Saturday, March 7.

## Related functions

- [VALUE](https://spreadsheetpractice.com/functions/value): VALUE converts text that looks like a number into a real number Excel can add, average and compare.
- [DATE](https://spreadsheetpractice.com/functions/date): DATE builds a real date from a year, month and day.
- [CONCAT](https://spreadsheetpractice.com/functions/concat): CONCAT joins text from several cells or whole ranges into one string.

## Questions

### Why does my date show as a number when I join it with text?

Dates are stored as serial numbers, and joining with & uses the stored value. Wrap the date in TEXT: ="Due "&TEXT(A2,"mmm d, yyyy").

### How do I add leading zeros in Excel?

=TEXT(A2,"00000") pads a number to five digits, so 412 becomes 00412. The result is text, which is what you want for ZIP codes and IDs.

## Practice

A graded TEXT exercise ("Month labels") is on [the TEXT page](https://spreadsheetpractice.com/functions/text). It is checked instantly, then re-checked on changed data.

Lessons that use TEXT:

- [Lesson 23: DATE, YEAR, MONTH & TEXT](https://spreadsheetpractice.com/learn/date-year-month-text) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
