How to Use the TEXT Function in Excel
Introduction
You have dates and dollar amounts in a report, but they need to look a specific way for labels, exports, or combined strings. The TEXT function converts numbers and dates into formatted text using custom format codes.
The Dataset
Here's an order log with dates, revenue figures, and product IDs:
| A | B | C | |
|---|---|---|---|
| 3 | Order Date | Revenue | Product ID |
| 4 | 2026-03-11 | 1500 | 42 |
| 5 | 2025-12-25 | 23750 | 7 |
| 6 | 2026-01-03 | 890 | 156 |
| 7 | 2026-07-04 | 12400 | 3 |
| 8 | 2025-11-15 | 5200 | 89 |
The raw numbers and dates work for calculations, but they need formatting for reports and labels.
TEXT
TEXT takes a value and a format code, and returns a formatted text string. The syntax is:
=TEXT(value, format_text)
- value: The number, date, or cell to format.
- format_text: A format code string that controls the output.
To display the date in A4 as a full month name:
=TEXT(A4, "mmmm d, yyyy")
The result is March 11, 2026. The format code controls which parts of the date appear and how.
TEXT always returns text, not a number, so you can't use the result in arithmetic.
Formatting Dates
Date format codes control how year, month, and day appear. Here's the same date (March 11, 2026) formatted four ways:
| Formula | Result |
|---|---|
=TEXT(A4, "mm/dd/yyyy") |
03/11/2026 |
=TEXT(A4, "mmmm d, yyyy") |
March 11, 2026 |
=TEXT(A4, "ddd, mmm d") |
Wed, Mar 11 |
=TEXT(A4, "dddd") |
Wednesday |
The number of letters in the code controls the output:
d/dd: Day number (5 or 05).ddd/dddd: Day name (Wed or Wednesday).m/mm: Month number (3 or 03).mmm/mmmm: Month name (Mar or March).yy/yyyy: Year (26 or 2026).
Separators like slashes, commas, and spaces are included literally in the output.
Formatting Numbers
To format the revenue column with dollar signs and commas:
=TEXT(B4, "$#,##0")
| Revenue | Formatted |
|---|---|
| 1500 | $1,500 |
| 23750 | $23,750 |
| 890 | $890 |
| 12400 | $12,400 |
| 5200 | $5,200 |
The key format characters for numbers:
#: Optional digit. Shows nothing if the position has no value.0: Required digit. Always shows, padding with zeros if needed.,: Thousands separator..: Decimal point.
Adding .00 at the end forces two decimal places: "$#,##0.00" turns 1500 into "$1,500.00".
Adding Leading Zeros
Product IDs like 42, 7, and 156 need to be the same width for barcodes or external systems. The format code "0000" pads each number to four digits:
=TEXT(C4, "0000")
| Product ID | Formatted |
|---|---|
| 42 | 0042 |
| 7 | 0007 |
| 156 | 0156 |
| 3 | 0003 |
| 89 | 0089 |
Each 0 in the format code represents a required digit position. If the number has fewer digits, Excel fills in zeros from the left.
Combining TEXT with Concatenation
TEXT is most useful when you're combining formatted numbers, dates, and plain text into a single string. The & operator joins text, and TEXT handles the formatting:
="Order " & TEXT(C4, "0000") & ": " & TEXT(B4, "$#,##0") & " on " & TEXT(A4, "mmm d, yyyy")
| Row | Result |
|---|---|
| 4 | Order 0042: $1,500 on Mar 11, 2026 |
| 5 | Order 0007: $23,750 on Dec 25, 2025 |
| 6 | Order 0156: $890 on Jan 3, 2026 |
| 7 | Order 0003: $12,400 on Jul 4, 2026 |
| 8 | Order 0089: $5,200 on Nov 15, 2025 |
Without TEXT, concatenating a date gives you the raw serial number instead of a readable date.
Key takeaway: TEXT is the function to use whenever you need formatted numbers or dates inside a text string.
Conclusion
TEXT formats numbers and dates as text using custom format codes. Use it for labels, exports, or anywhere you need values to display a specific way.
For other ways to work with dates, see our guide on date functions. For combining text from multiple cells, check out our guide on TEXTJOIN.
Level up your Excel skills
Bite-sized lessons, drills, and daily challenges to build real spreadsheet skills.