The Excel TEXT function, explained interactively.
TEXT converts a number or date into a formatted text string using Excel’s number-format syntax. It’s the bridge between a raw value and how it should appear inside a sentence, label, or compound formula — preserving currency symbols, date separators, and leading zeros that Excel would otherwise strip. The trade-off: the result is always text, so arithmetic on the output will fail unless you wrap it back with VALUE().
How to use TEXT
Give TEXT a number or date and a format code — it returns the value as a formatted string. Open the playground to experiment with the format code live.
- Type
=TEXT(and supply the value — a cell reference, a number, or a formula that returns a number or date serial. Do not quote the number. - Add a comma, then the format_text — a quoted string using Excel’s number-format syntax:
"$#,##0.00"for currency,"YYYY-MM-DD"for ISO dates,"00000"for zero-padded IDs. - Close the parenthesis. The result is a text string, not a number — you can join it with
&or CONCATENATE, but you cannot add it to another number without first callingVALUE(). - Build dynamic labels by combining TEXT with
&:="Sales: " & TEXT(B2, "$#,##0")produces a header that updates automatically as B2 changes.
"$#,##0.00" for currency, "YYYY-MM-DD" for ISO date, "00000" for zero-padded IDs.| A | B | |
|---|---|---|
| 1 | Value | Format code |
| 2 | 12345.678 | "$#,##0.00" |
| 3 | ||
| 4 | TEXT result | $12,345.68 |
TEXT syntax and arguments
Two required arguments — no optional ones. The entire power of TEXT lives in the format code string. See Microsoft’s official TEXT reference for the full list of format codes.
A2), a literal number (1234.5), or a formula that returns a number — such as TODAY(), SUM(B2:B8), or an arithmetic expression. Passing a text string that does not represent a valid number returns a #VALUE! error. Passing a logical (TRUE/FALSE) returns the text "TRUE"/"FALSE" regardless of the format code.#— show digit if present, omit if zero0— always show digit, pad with zero if absent,inside a number mask — thousand separator (e.g.#,##0).— decimal pointYYYY/MM/DD— date parts (uppercase;mmlowercase = minutes)MMM— abbreviated month name (Jan–Dec)MMMM— full month name (January–December)HH:MM:SS— time; hereMMafterHH:means minutes in context%— multiply by 100 and add a percent sign- Any literal character — wrap in double quotes within the format code, or prefix with a backslash:
\-
TEXT examples
Four patterns that cover the most common TEXT use cases.
Example 1: TEXT for currency formatting
The format code "$#,##0.00" adds a dollar sign, thousand-separator commas, and exactly two decimal places. This is the most common TEXT use case — presenting a raw number as a human-readable currency amount inside a string.
Returns "$1,234.50". The result is a text string — it cannot be added to another number. Use this inside a label: ="Total: " & TEXT(A2, "$#,##0.00").
Example 2: TEXT to convert a date serial to a readable string
Excel stores dates as numbers (date serials). Without TEXT, joining a date cell with & exposes the raw serial number (e.g. 45383). TEXT converts it to the format you specify.
Returns "March 3, 2024". Try "DD/MM/YYYY" for European style or "YYYY-MM-DD" for ISO 8601. The serial number 45383 maps to March 3, 2024 in both Excel and Google Sheets.
Example 3: TEXT to zero-pad numbers (IDs, SKUs)
A format code of all zeros forces a fixed-width output. Each 0 is a required digit position — if the number is shorter, TEXT pads it with leading zeros to fill the width.
Returns "00042". Standard technique for product codes and employee IDs that must sort correctly as text strings. Change the format to "000" for 3-digit codes, "0000000" for 7-digit, etc.
Example 4: TEXT with & for dynamic dashboard labels
Combine TEXT with the & operator to build labels that update as the underlying data changes. A common pattern in dashboards, report headers, and email-template cells.
Returns "Sales: $48,750" when B2 is 48750. Without TEXT, & would produce "Sales: 48750" — no dollar sign, no commas. Works identically in Google Sheets.
Common TEXT errors and fixes
Four failure modes — two produce visible errors, two produce silently wrong output that looks correct until you inspect it.
TEXT returns #VALUE!
Cause: the value argument is a text string that cannot be interpreted as a number — for example, TEXT("hello", "0") or a cell containing text when you expected a number. A completely unparseable format string can also trigger this.
Check that the first argument resolves to a number or valid date serial. Use =ISNUMBER(A2) to verify the source cell. If the cell contains a number stored as text, apply VALUE(A2) first: =TEXT(VALUE(A2), "$#,##0.00").
TEXT date format shows wrong output — "MM" vs "mm"
Cause: Excel’s format codes are case-sensitive for date parts. Lowercase mm means minutes; uppercase MM means months. Using TEXT(date, "mm/dd/yyyy") returns "00/03/2024" — zero minutes, not the month. This is the single most common TEXT date bug.
Always use uppercase MM for months in date formats. Memory trick: MM for Months, mm is tucked below — just like minutes come after hours in a clock display.
TEXT result can’t be added — arithmetic fails silently
Cause: TEXT always returns a text string. Passing the result to SUM, a math operator (+, *, etc.), or any numeric function will either return 0 (SUM ignores text) or a #VALUE! error (direct arithmetic).
If you need the formatted string and numeric calculations, keep the raw number in one cell for math, and use TEXT only in a separate display cell. To recover a number from a TEXT result, wrap it in VALUE() — but this only works if the format code produced an unambiguous numeric string.
TEXT loses the original value — VALUE() can’t always reverse it
Cause: once a number is converted to text with an abbreviating format code — such as "$#,##0" (which drops decimals) or "MMM-YY" (which loses the day) — the information is irreversibly gone from the text string. Wrapping the result in VALUE() either errors or returns a rounded integer, not the original value.
Store the source number separately if you ever need it again. TEXT is a one-way street: number → text, not the reverse. Use VALUE() only when the format code preserved every digit (e.g. "0.00" on 1234.56 → "1234.56" → VALUE → 1234.56).
TEXT vs custom number formatting, VALUE() & TO_TEXT
TEXT is one of several ways to control how numbers look. Pick the right tool for your situation.
| Approach | Returns text? | Arithmetic on result? | Available in | Best for |
|---|---|---|---|---|
TEXT(value, format) | Yes — always | No (need VALUE()) | Excel & Sheets | Embedding formatted numbers in labels, sentences, or compound formulas |
| Custom cell format (Format Cells dialog) | No — number stays numeric | Yes | Excel & Sheets | Display-only formatting; cell value stays numeric, safe for arithmetic |
VALUE(text) | No — returns number | Yes | Excel & Sheets | Reverse-converting a text string back to a number; fails if the string contains symbols VALUE can’t parse |
TO_TEXT(value) | Yes — no format control | No | Google Sheets only | Simple coercion to text with no formatting; Google Sheets-specific convenience wrapper |
Rule of thumb: if the cell only needs to look formatted, use a custom cell format (Format Cells → Number) and keep the value numeric. Use TEXT only when the formatted value needs to be embedded in a string — a label, sentence, filename, or argument to another text function. Mixing TEXT results back into arithmetic is the most common source of unexpected zeros.
TEXT frequently asked questions
6.01How do I format a number with commas using TEXT?▸
Use the format code "#,##0" for whole numbers or "#,##0.00" for two decimal places. Prepend a currency symbol to get "$#,##0.00". The comma inside the number mask triggers Excel’s thousand-separator grouping — it doesn’t literally place a comma, it tells Excel to insert one every three digits from the right. For example, TEXT(1234567.8, "$#,##0.00") returns "$1,234,567.80".
6.02What format code do I use for dates in TEXT?▸
Common date format codes:
"YYYY-MM-DD"→ ISO 8601 (2024-03-03)"DD/MM/YYYY"→ European (03/03/2024)"MM/DD/YYYY"→ US style (03/03/2024)"MMMM D, YYYY"→ written out (March 3, 2024)"MMM-YY"→ compact (Mar-24)"DDDD"→ day of week (Sunday)
Always use uppercase MM for months. Lowercase mm means minutes.
6.03Why does TEXT(date, "MM") show numbers instead of month names?▸
"MM" returns the two-digit month number (01–12), not the name. For abbreviated names use "MMM" (Jan, Feb, …); for full names use "MMMM" (January, February, …). And be careful with case: lowercase "mm" means minutes — a date serial has zero minutes, so you’d see "00" instead of the month number. Excel’s date format codes are case-sensitive.
6.04Can I use TEXT to add leading zeros?▸
Yes — this is one of the most reliable uses of TEXT. Use a format code containing exactly as many zeros as the total width you need: TEXT(42, "00000") returns "00042", TEXT(7, "000") returns "007". If the number is already at or above the specified width, TEXT returns it as-is without truncating: TEXT(123456, "00000") returns "123456" (6 digits, not truncated to 5).
6.05Does TEXT work the same in Google Sheets?▸
Yes. TEXT(value, format_text) has the same syntax and behaviour in Google Sheets. Nearly all common format codes work identically: "$#,##0.00", "YYYY-MM-DD", "00000", and so on. One difference: Google Sheets also supports TO_TEXT() for simple coercion with no format control, which has no Excel equivalent. The date serial epoch (December 30, 1899) is the same in both, so date serials produced in one application work in the other.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.