What TEXT Does
The TEXT function converts a number, date, or time into formatted text using a pattern you specify. It is the bridge between raw data and human-readable output — letting you display currency with symbols, dates in any format, percentages with specific decimal places, and more. The result is always a text string, which means it cannot be used in further arithmetic without converting it back.
Syntax
=TEXT(number, format)
| Parameter | Description |
|---|---|
| number | The numeric value, date, or cell reference to format. |
| format | A format pattern string enclosed in quotes. |
Common format codes
| Code | Meaning | Example output |
|---|---|---|
"0" | Digit (shows zero if absent) | TEXT(5, "000") → 005 |
"#" | Digit (omits if absent) | TEXT(5, "###") → 5 |
"#,##0" | Thousands separator | TEXT(42000, "#,##0") → 42,000 |
"$#,##0.00" | Currency | TEXT(1250, "$#,##0.00") → $1,250.00 |
"0.0%" | Percentage | TEXT(0.875, "0.0%") → 87.5% |
"YYYY-MM-DD" | ISO date | TEXT(A1, "YYYY-MM-DD") → 2026-04-05 |
"MMMM D, YYYY" | Long date | TEXT(A1, "MMMM D, YYYY") → April 5, 2026 |
"HH:MM AM/PM" | 12-hour time | TEXT(A1, "HH:MM AM/PM") → 02:30 PM |
Basic Examples
Format a number as currency
| A | B | |
|---|---|---|
| 1 | Amount | Formatted |
| 2 | 1499.5 | =TEXT(A2, "$#,##0.00") |
Result in B2: $1,499.50
Display a date in a specific format
| A | B | |
|---|---|---|
| 1 | Date | Readable |
| 2 | 4/5/2026 | =TEXT(A2, "MMMM D, YYYY") |
Result in B2: April 5, 2026
Show a percentage with one decimal
| A | B | |
|---|---|---|
| 1 | Rate | Display |
| 2 | 0.0823 | =TEXT(A2, "0.0%") |
Result in B2: 8.2%
Advanced Examples
Build a readable summary sentence
Combine TEXT with concatenation to create dynamic sentences:
="Revenue for " & TEXT(A2, "MMMM YYYY") & " was " & TEXT(B2, "$#,##0") & ", up " & TEXT(C2, "0.0%") & " from last month."
For A2 = 4/1/2026, B2 = 84500, C2 = 0.12, this produces:
Revenue for April 2026 was $84,500, up 12.0% from last month.
This is especially useful for dashboard labels and report headers that need to update automatically.
Pad numbers with leading zeros
If you are working with ZIP codes, employee IDs, or invoice numbers that need a fixed width:
=TEXT(A2, "00000")
For A2 = 725, this returns 00725. Without TEXT, Google Sheets would strip the leading zeros since it treats the value as a number.
Common Mistakes
- Using the TEXT result in calculations. TEXT returns a string, not a number.
=TEXT(100, "$#,##0") + 50will produce an error. Keep the raw numbers for math and use TEXT only for display purposes. - Mixing up format codes for dates.
"MM"is months,"mm"is minutes. Writing=TEXT(A1, "YYYY-mm-DD")when you mean a date will give unexpected results. Use uppercase M for months and lowercase m for minutes within time formats. - Forgetting locale differences. Format codes like
"$#,##0.00"use the dollar sign literally. For other currencies, type the symbol directly:=TEXT(A1, "€#,##0.00"). The thousands and decimal separators follow the spreadsheet locale, not the format string.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English and the formula is generated for you. Install SheetAI to try it free.