TEXT Function: Format Numbers as Text in Excel

Have you ever tried to combine a number or date with a text label in Excel, only to end up with something like "Revenue: 48293.5" or "Report date: 45012"? That's Excel's raw data leaking into your output — and it's exactly the problem the TEXT function was built to solve.

The TEXT function converts a numeric value into a formatted text string. It gives you precise control over how numbers, currency, percentages, dates, and times appear — whether you're building reports, creating labels, or assembling dynamic sentence-style outputs. This guide walks you through everything you need to know, from basic syntax to practical real-world examples.

TEXT Function Syntax

The syntax is straightforward:

=TEXT(value, format_text)

• value — The number, date, or cell reference you want to format.
• format_text — A format code, enclosed in double quotes, that tells Excel how to display the value.

The result is always a text string, not a number. This means you cannot use the output of TEXT in arithmetic calculations without first converting it back to a number.

Format Codes: The Core of TEXT

Format codes are the same codes you see in Excel's "Format Cells" dialog (Ctrl + 1). You can use them directly inside the TEXT function. Here are the most common building blocks:

Code	Meaning	Example Input	Example Output
0	Digit placeholder (shows zeros)	7	"007" with "000"
#	Digit placeholder (no extra zeros)	1234.5	"1234.5" with "#.#"
,	Thousands separator	1000000	"1,000,000" with "#,##0"
.	Decimal point	3.14159	"3.14" with "0.00"
%	Percentage (multiplies by 100)	0.75	"75.0%" with "0.0%"
$	Currency symbol (literal)	2500	"$2,500" with "$#,##0"
d / dd	Day (1 or 2 digits)	Date	"5" or "05"
mmm / mmmm	Month (abbreviated/full)	Date	"Jan" or "January"
yyyy	4-digit year	Date	"2025"
h:mm AM/PM	Time with AM/PM	Time value	"9:05 AM"

Formatting Numbers: Common Examples

Adding Thousands Separators and Decimals

=TEXT(A2, "#,##0.00")

If A2 contains 48293.5, this formula returns "48,293.50" — clean, readable, and ready to use in a report label.

Displaying Currency

=TEXT(A2, "$#,##0.00")

Turns 2500 into "$2,500.00". Useful when combining numbers with text, such as:

="Total Revenue: "&TEXT(B5,"$#,##0")

This produces: Total Revenue: $48,294

Formatting Percentages

=TEXT(A2, "0.0%")

If A2 is 0.876, the result is "87.6%". Ideal for building KPI summary sentences in Sales Dashboards in Excel.

Padding Numbers with Leading Zeros

=TEXT(A2, "00000")

Useful for employee IDs, product codes, or ZIP codes. Turns 742 into "00742".

Formatting Dates with TEXT

Dates in Excel are stored as serial numbers (e.g., January 1, 2025 = 45658). The TEXT function lets you display them in any readable format.

Common Date Format Examples

=TEXT(A2, "dd/mm/yyyy")      → "14/06/2025"
=TEXT(A2, "mmmm d, yyyy")   → "June 14, 2025"
=TEXT(A2, "ddd, mmm d")     → "Sat, Jun 14"
=TEXT(A2, "yyyy-mm-dd")     → "2025-06-14"

This is especially handy when building report headers or timeline labels. For example, if you're working on a Project Timeline or Gantt Chart in Excel, formatted date labels make your chart far more readable.

Combining Dates with Text

="Report generated on: "&TEXT(TODAY(),"mmmm d, yyyy")

Result: Report generated on: June 14, 2025

Without TEXT, this would produce "Report generated on: 45658" — which is useless to any reader.

Formatting Times with TEXT

Times in Excel are fractional numbers (e.g., 0.5 = noon). TEXT handles them cleanly:

=TEXT(A2, "h:mm AM/PM")     → "9:05 AM"
=TEXT(A2, "hh:mm:ss")       → "09:05:32"
=TEXT(A2, "[h] hours")      → "14 hours" (elapsed time)

The [h] format is particularly useful for elapsed time — it lets hours exceed 24, which is critical for tracking hours worked over multiple days.

Practical Walkthrough: Building a Dynamic Invoice Summary

Let's say you're building an invoice and want a clean summary line that combines text and numbers. Here's the setup:

• A1: Invoice Date → B1: 2025-06-14 (a real date value)
• A2: Amount Due → B2: 3750
• A3: Due In → B3: 30 (days)

You want to generate this sentence automatically:

"Invoice dated June 14, 2025 for $3,750.00 is due in 30 days."

Here's the formula:

="Invoice dated "&TEXT(B1,"mmmm d, yyyy")&" for "&TEXT(B2,"$#,##0.00")&" is due in "&B3&" days."

This single formula dynamically assembles the entire sentence. Change the date, amount, or days — the sentence updates automatically. This kind of dynamic labeling is invaluable for professional billing and invoice templates.

TEXT vs. Formatting Cells Directly

A common question: why not just format the cell using the Format Cells dialog? Here's the key difference:

• Cell formatting changes how a number looks but the underlying value stays numeric. You can still use it in calculations.
• TEXT function converts the value to actual text. The result cannot be used in SUM, AVERAGE, or other math functions — but it can be concatenated into strings, and it displays exactly as specified regardless of user locale settings.

Use TEXT when you need to embed numbers in sentences, build consistent export formats, or ensure formatting is locked in regardless of regional settings. Use cell formatting when you still need the number to behave like a number.

Combining TEXT with Other Functions

TEXT becomes even more powerful when combined with other Excel functions.

TEXT + IF

=IF(B2>0, "Profit: "&TEXT(B2,"$#,##0"), "Loss: "&TEXT(ABS(B2),"$#,##0"))

This creates a labeled output that adapts to positive or negative values. If you want to explore conditional logic further, see our guide on the IF Function: Logical Tests and Nested IFs.

TEXT + VLOOKUP

="Order "&TEXT(VLOOKUP(A2,OrderTable,2,0),"00000")&" shipped."

Pulls an order number and pads it to 5 digits inside a sentence — all in one formula.

TEXT + TODAY / NOW

="As of "&TEXT(TODAY(),"dddd, mmmm d")&" - data is current."

Creates a live-updating timestamp label in your dashboard header. Pair this with dynamic dashboards in Excel for professional, self-updating reports.

Common Mistakes to Avoid

• Forgetting the double quotes around the format code. =TEXT(A2, #,##0) will throw an error. Always wrap format codes in quotes: =TEXT(A2, "#,##0").
• Using TEXT output in calculations. Since the result is text, =TEXT(A2,"0.00")+10 will error or return 0. Perform calculations first, then wrap in TEXT.
• Locale-dependent symbols. If you hardcode "$" but users are in Europe, consider whether you need a locale-neutral approach or a dynamic currency symbol from another cell.
• Wrong month vs. minute codes. Both use "m" — Excel interprets "m" as minutes when it immediately follows hours in a time format, and as months otherwise. Use "mm" carefully in mixed date-time formats.

Get TEXT Formulas Instantly with GPTExcel

If you're unsure which format code to use for a specific output — say, you want numbers displayed as "1st", "2nd", "3rd" or need a custom fiscal date format — you can describe what you want in plain English to GPTExcel and get the exact TEXT formula (format code and all) in seconds, without having to memorize every code combination.

For more ways to simplify formula writing and data preparation, check out Using AI to Clean and Transform Data in Excel.

Frequently Asked Questions

Why does my TEXT formula return a number instead of formatted text?

This usually means the cell is formatted as "Number" and Excel is converting the text result back to a number. Select the cell, format it as "Text" (or "General"), and re-enter the formula. Also double-check your format code is wrapped in double quotes.

Can I use TEXT to format negative numbers differently?

Yes. Excel format codes support up to four sections separated by semicolons: positive; negative; zero; text. For example: "$#,##0.00;($#,##0.00);"-"" shows positive values in standard format, negatives in parentheses, and zeros as a dash.

Does TEXT work with dates in all Excel versions?

Yes. The TEXT function and its date format codes (d, dd, mmm, mmmm, yy, yyyy, etc.) are supported in all modern versions of Excel — including Excel 2010, 2013, 2016, 2019, 2021, Microsoft 365, and Excel Online. The output may vary slightly if regional date settings differ between users.

What's the difference between TEXT and NUMBERVALUE?

They do opposite things. TEXT converts a number into a formatted text string. NUMBERVALUE converts a text string that looks like a number back into an actual numeric value. Use NUMBERVALUE (or VALUE) when you need to reverse the effect of TEXT and do math on the result again.