Text
How to Use the TEXT Function in Excel (Format Codes)
The TEXT function turns a number, date, or time into text formatted exactly the way you want: $1,250.00, 25%, 2026-06-30, or an ID with leading zeros. You hand it a value and a format code, and it returns a text string that looks the way you specified.
It is the function to reach for when you need to control how a value appears inside a sentence, a label, or a joined string, since the underlying cell formatting is lost the moment you combine values. This guide covers the syntax, the most useful format codes, and the errors to watch for.
TEXT syntax
=TEXT(value, format_text) | Argument | Required | What it does |
|---|---|---|
value | Required | The number, date, or time you want to format. |
format_text | Required | The format code, in quotes, that describes how the result should look, for example "$#,##0.00" or "yyyy-mm-dd". |
Note: The result is text, not a number, so you cannot do math on it. Format codes follow the same patterns as the Format Cells dialog: 0 is a required digit, # is an optional digit, and yyyy/mm/dd are date parts. Works in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Format a number as currency
=TEXT(A2, "$#,##0.00") The code "$#,##0.00" adds a dollar sign, a thousands comma, and two decimal places. The result is text that reads $1,250.00.
2. Format a date
=TEXT(A2, "yyyy-mm-dd") yyyy is the four-digit year, mm the two-digit month, and dd the two-digit day. Change the order and separators to get any date layout you like.
3. Add leading zeros to an ID
=TEXT(A2, "000000") Each 0 is a required digit, so a short number is padded on the left with zeros to a fixed width. Ideal for invoice or product codes.
4. Use a formatted number inside a sentence
="Total: "&TEXT(A2, "$#,##0.00") Joining a raw number drops its formatting, so wrap it in TEXT first. The & glues the label and the formatted amount together.
How to write TEXT step by step
- 1
Click the result cell and type =TEXT(
- 2
Click the number, date, or time you want to format, then type a comma.
- 3
Type the format code in quotes, for example "$#,##0.00" for currency or "yyyy-mm-dd" for a date.
- 4
Close the bracket ) and press Enter.
- 5
To use the result in a sentence, join it with &, e.g. ="Due: "&TEXT(A2, "mmm d").
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#NAME? | The format code was not wrapped in quotes. | Always put the format_text in double quotes, e.g. TEXT(A2, "0.00"). |
Result cannot be summed | TEXT returns text, so SUM and other math ignore it. | Keep the original number for calculations and use TEXT only for display, or multiply the result by 1 to convert it back. |
Date shows a serial number | A date was joined to text without TEXT, so it appears as 46203. | Wrap the date in TEXT with a date code, e.g. TEXT(A2, "yyyy-mm-dd"). |
Wrong decimal or comma | The format code uses symbols your regional settings interpret differently. | Match the code to your locale, or use the # and 0 placeholders, which behave consistently. |
Frequently asked questions
What does the TEXT function do in Excel?
It converts a number, date, or time into a text string formatted with a code you supply, such as "$#,##0.00" or "yyyy-mm-dd". It controls how a value looks, especially when you join it to other text.
Why does my number lose its formatting when I join cells?
Cell formatting is only for display; the underlying value is a plain number. When you join it with & or CONCAT, Excel uses the raw number, so wrap it in TEXT to keep the look you want.
Can I do math on the result of TEXT?
Not directly, because TEXT returns text. Keep the original number for calculations, or convert the text back to a number by multiplying it by 1 or using VALUE().
What is the format code for a percentage?
Use "0%" for a whole percent or "0.0%" for one decimal place, e.g. =TEXT(A2, "0.0%") turns 0.125 into 12.5%.