The TEXT function converts a number or date into text using a format code you specify. It is useful when you need to combine numbers with text or display values in a custom format.
=TEXT(value, format_text)What does TEXT do?
The TEXT function in Excel turns a number, date, or time into a text string that looks exactly the way you want. You give it a value and a format code, and it returns the formatted result as text. For example, =TEXT(1234.5, "$#,##0.00") returns $1,234.50 as text, not as a number.
Why does this matter? Because Excel treats numbers and text differently. When you join a number with words using & or CONCATENATE, the number loses its formatting. TEXT keeps the look you want inside the combined string. It also helps when you need to show dates in a specific style, add leading zeros, or present percentages in a sentence.
Imagine you are writing a summary line like Total sales: $12,345.67 or a label like Due date: 15-Jan-2025. Without TEXT, the number or date may appear as a raw serial number or with unwanted decimals. With TEXT, you control the exact appearance. This makes reports, dashboards, and student assignments look professional and consistent.
When to use TEXT
- Combining numbers with text in a single cell, such as
"Total: "&TEXT(A1,"$#,##0"). - Displaying dates in a custom format like
dd-mmm-yyyyinside a sentence. - Adding leading zeros to ID numbers or codes, e.g.,
TEXT(42,"00000")gives00042. - Formatting percentages or decimals for labels and chart titles.
- Creating dynamic messages that include formatted values, such as
"You scored "&TEXT(B2,"0.0%").
TEXT syntax
=TEXT(value, format_text)| Argument | Required? | What to enter |
|---|---|---|
value | Required | The number, date, or cell reference you want to format. Example: A2 or 1234.5. |
format_text | Required | A text string in double quotes that defines the format. Example: "$#,##0.00" or "dd/mm/yyyy". |
What it returns: A text string that represents the value formatted according to the format code.
TEXT examples
1 Format a number with two decimals
This example shows how to display a plain number with exactly two decimal places. The formula =TEXT(B2,"0.00") takes the value in B2 and applies the format code 0.00, which forces two digits after the decimal point. It returns the text 1234.50. Use this when you need consistent decimal places in labels or combined text.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Value | |||
| 2 | Widget | 1234.5 | 1234.50 | ||
| 3 | Gadget | 987.6 | |||
| 4 |
2 Add a currency symbol and thousands separator
Here you want to show sales amounts as currency with commas. The formula =TEXT(B2,"$#,##0.00") formats the number in B2 with a dollar sign, thousands separator, and two decimals. It returns $1,200.00 as text. This is handy when building sentences like Total: $1,200.00.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | $1,200.00 | ||
| 3 | South | 950.5 | |||
| 4 |
3 Format a date as dd-mmm-yyyy
Dates in Excel are stored as serial numbers, so joining them with text can produce unexpected results. The formula =TEXT(B2,"dd-mmm-yyyy") converts the date in B2 into a readable text string like 15-Jan-2025. The format code dd-mmm-yyyy uses two-digit day, three-letter month, and four-digit year. Use this for report headers or labels.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Event | Date | |||
| 2 | Meeting | 2025-01-15 | 15-Jan-2025 | ||
| 3 | Deadline | 2025-02-28 | |||
| 4 |
4 Create a combined label with text and formatted number
You often need a single cell that says something like Total: $1,200.00. The formula ="Total: "&TEXT(B2,"$#,##0.00") joins the literal text Total: with the formatted value from B2. TEXT ensures the number keeps its currency format inside the string. The result is the text Total: $1,200.00.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | Total: $1,200.00 | ||
| 3 | South | 950.5 | |||
| 4 |
5 Format a percentage for a score message
This example turns a decimal score into a percentage inside a sentence. The formula ="Score: "&TEXT(B2,"0.0%") formats the value in B2 as a percentage with one decimal place. It returns Score: 85.0%. The % in the format code multiplies the number by 100 and adds the percent sign. Use this for student feedback or performance summaries.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | |||
| 2 | Alice | 0.85 | Score: 85.0% | ||
| 3 | Bob | 0.92 | |||
| 4 |
Common TEXT errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The format_text argument is not a valid text string, often because it is missing double quotes or contains an invalid format code. | Enclose the format code in double quotes, e.g., "0.00", and ensure it uses valid Excel format characters. |
| #NAME? | The function name is misspelled, such as TEXTT or TEXT( without closing parenthesis. | Check the spelling: use TEXT and make sure parentheses are balanced. |
| Unexpected text result | The format code does not match the type of value, e.g., using a date format on a plain number. | Choose a format code appropriate for the value type, such as 0.00 for numbers or dd/mm/yyyy for dates. |
| Numbers stored as text | After using TEXT, the result is text, so later calculations may ignore it or produce errors. | Use TEXT only for display. If you need to calculate, keep the original numeric value in a separate cell. |
Tips for using TEXT
- Use
TEXTwhen you need to combine numbers or dates with words and keep a specific appearance. - Remember that the result of
TEXTis always text, so it cannot be used directly in arithmetic without conversion. - For dates, use format codes like
dd/mm/yyyyormmm d, yyyyto avoid ambiguous displays. - To add leading zeros, use a format code with zeros, e.g.,
"00000"for a five-digit code. - You can use
TEXTinside other functions likeCONCATENATEor&to build dynamic labels. - Test your format code on a sample cell first to ensure it produces the expected text.
Frequently asked questions
What is the difference between TEXT and changing cell formatting?
Changing cell formatting only affects how the value is displayed; the underlying value remains a number. TEXT converts the value to a text string, which is useful when combining with other text.
Can I use TEXT to format dates?
Yes. Use format codes like "dd-mmm-yyyy" or "mmmm d, yyyy" to convert a date serial number into a readable text date.
Why does TEXT return a number stored as text?
TEXT always returns text. If you need to perform calculations later, keep the original numeric value in a separate cell or convert the text back to a number.
How do I include a literal text character in the format code?
Enclose literal characters in double quotes within the format code, or use a backslash before the character. For example, "$#,##0.00" includes the dollar sign.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. TEXT reference