TEXT
Turns a number into formatted text using a format code: the way to bake $, %, or padded zeros right into a displayed value.
TEXT returns a text string, not a number, and that's the whole trade-off in one sentence: the result looks exactly right, formatted with a currency symbol or a percent sign or leading zeros, but it can no longer be added up or compared numerically without converting it back first. That trade-off is the entire point of the function: TEXT is for building a label or a sentence that needs a number formatted a specific way inside it, not for formatting a number that's still going to be calculated with (that's a cell's Number Format instead, covered on the Cell Formatting page).
Syntax
| Argument | Description |
|---|---|
| value | Required. The number (or a cell holding one) to format. |
| format_text | Required. A format code in quotes, e.g. "$#,##0.00", "0%", or "000000". |
A real example: a ticket number padded to six digits
An internal ticket ID that should always display as six digits, even for small numbers:
42 becomes "000042"; the padding is purely cosmetic for display or for concatenating into a larger label, and the result can no longer be used in arithmetic without converting it back to a number first.
Worked examples
| A | |
|---|---|
| 1 | Price |
| 2 | 1234.5 |
| A | |
|---|---|
| 1 | Rate |
| 2 | 0.0725 |
| A | |
|---|---|
| 1 | Ticket |
| 2 | 42 |
Common mistakes
- Feeding a TEXT result back into more arithmetic. It's a string now: wrap it with VALUE() first, or just keep a separate numeric cell for calculations and a TEXT cell for display.
- Mistyping a format code (a stray comma or missing #) and getting an unhelpful result instead of a clear error; double-check the code against a known-good example.
- Using TEXT where a cell's own Number Format would do the job. TEXT is for when the formatted value needs to live inside a larger text string, like a sentence.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice TEXT →