Excel TEXT Function

TEXT converts a number, date, or time into a text string formatted the way you specify. The result looks like a formatted value, but it isn't one: TEXT always returns text, even when the output is made entirely of digits. That single fact causes most of the confusion around this function, so keep it in mind before you build anything with it.

Available in: Excel 365, Excel 2021, Excel 2019, Excel 2016, and earlier versions, plus Excel Online and Excel for Mac. TEXT has no version restrictions worth worrying about.

Syntax

=TEXT(value, format_text)

Parameter	Required	Description
`value`	Yes	The number, date, or time you want to convert. Can be a cell reference, a formula result, or a literal value.
`format_text`	Yes	The format code that controls how the output looks, entered as text in quotes. Uses the same codes you'd type into the Format Cells dialog: `"0.00"`, `"mm/dd/yyyy"`, `"$#,##0"`, and so on.

⚠

TEXT always returns text, not a number. Formulas that add, average, or otherwise calculate on a TEXT result will either error out or silently coerce it back to a number, stripping your formatting. If you need the number to stay usable in math, apply number formatting to the cell instead and leave the underlying value untouched.

Basic Example

You're building a summary row for a shipping report and need order totals to appear as currency inside a single text string, not in a separate formatted column.

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

// B2           = 1249.99 (the raw order total)
// "$#,##0.00"  = format code for currency with a thousands separator and two decimals

With B2 holding 1249.99, this returns the text string "$1,249.99". Notice the result is left-aligned by default in the cell, a visual tell that Excel treats it as text rather than a number. That's expected; the whole point of TEXT is to lock formatting into a string you can concatenate, export, or display exactly as written, independent of the cell's own number format.

How TEXT Works

Format codes control every part of the output

The second argument is a format code string, the same syntax used in the custom Format Cells dialog. "0" returns a whole number, "0.00" forces two decimal places, "#,##0" adds thousands separators, and "0%" multiplies by 100 and appends a percent sign. Stack multiple format elements in one code to control decimals, separators, and symbols in a single pass.

Format code	Input	Result
`"0"`	1249.99	1250
`"0.00"`	1249.99	1249.99
`"#,##0"`	1249.99	1,250
`"$#,##0.00"`	1249.99	$1,249.99
`"0%"`	0.865	87%
`"00000"`	42	00042
`"mm/dd/yyyy"`	3/8/2026	03/08/2026
`"dddd, mmmm d, yyyy"`	3/8/2026	Sunday, March 8, 2026
`"h:mm AM/PM"`	0.5417	1:00 PM

Dates and times use letter codes, not number codes

Excel stores dates as serial numbers and times as fractions of a day, so TEXT needs date-specific codes to render them as calendar dates instead of raw integers. "yyyy" returns a four-digit year, "mmmm" spells out the month name, "ddd" gives a three-letter weekday abbreviation, and "h:mm:ss" formats a time with hours, minutes, and seconds.

=TEXT(C2,"mmmm d, yyyy")

// C2 = 7/13/2026
// Returns "July 13, 2026"

TEXT always returns a string, never a number

This is the one behavior every intermediate user eventually gets burned by. A range of TEXT results dropped into =SUM(...) silently skips every one of them, since SUM ignores text values rather than throwing an error, so a total referencing a column of TEXT output quietly comes back lower than expected instead of flagging the problem. If you need both a formatted display and a usable number, keep them in separate cells: one with number formatting applied normally, one with TEXT for display or export purposes.

Format codes are locale-sensitive

The letter m means month in a date context but minutes in a time context, and Excel decides which one you mean based on what's nearby in the code. "h:mm" reads m as minutes because it follows an hour code. "mmm-yy" reads it as month because there's no hour code adjacent. Get the order wrong and TEXT returns a value that looks plausible but isn't what you meant, with no warning.

Combining TEXT with concatenation builds dynamic labels

Because TEXT returns a string, it slots directly into & concatenation or TEXTJOIN without any extra conversion step. This is the pattern behind most dashboard titles and chart labels that update automatically as source data changes.

="Total Sales: " & TEXT(SUM(sales_totals),"$#,##0")

// Returns something like "Total Sales: $84,210"
// Updates automatically whenever sales_totals changes

✓

For labels with more than two or three pieces, TEXTJOIN reads cleaner than a long chain of & operators, especially once you start nesting multiple TEXT calls inside one string.

Common Use Cases

Padding invoice or ID numbers with leading zeros

A finance team wants invoice numbers to always display as eight digits, even when the underlying number is short.

=TEXT(A2,"00000000")   // 1042 becomes "00001042"

Building a merge-ready summary line

You're generating a batch of shipment confirmations and need one text string per row combining a date, an order number, and a total.

="Order " & B2 & " shipped " & TEXT(C2,"mmmm d, yyyy") & " for " & TEXT(D2,"$#,##0.00")

// B2  = order number, plain text concatenation
// C2  = ship date, formatted as "July 13, 2026"
// D2  = order total, formatted as currency

Grouping dates by month name for a summary table

Instead of a helper column with a date formula, pull just the month name to group transactions without altering the original date values.

=TEXT(E2,"mmmm")   // returns "July" from a date like 7/13/2026

Formatting percentages for a report footer

A commission tracker needs a plain-language sentence stating the current close rate, pulled from a raw decimal.

="Close rate: " & TEXT(closed_deals/total_leads,"0.0%")

// closed_deals/total_leads = 0.4286 as a raw decimal
// Returns "Close rate: 42.9%"

Handling Errors

TEXT rarely fails outright, but it can return #VALUE! when it can't interpret either argument.

Common causes of #VALUE!:

value points to a cell containing text that isn't a number or date (a name, a blank, a label)
format_text isn't wrapped in quotes, so Excel reads it as a name or reference instead of a code
value references a cell that already contains an error, like #REF! or #N/A

=IFERROR(TEXT(A2,"mm/dd/yyyy"), "Invalid date")

✓

If a column mixes real dates with text like "TBD" or "Pending," wrap TEXT in IFERROR rather than trying to clean the source data first. It keeps the report generating even when a few rows aren't ready.

Notes & Gotchas

What is the TEXT function used for?

TEXT converts numbers, dates, and times into formatted text strings so they can be combined with other text, displayed in a specific format regardless of the cell's own number formatting, or exported somewhere that only accepts plain strings. It's most common in dashboard titles, merge letters, and any formula that concatenates a value into a sentence.

How do I use the TEXT function in Excel?

Wrap the value you want to format in TEXT(), followed by a comma and a format code in quotes: =TEXT(A2,"0.00"). The format code determines everything about the output, from decimal places to currency symbols to date structure, so most of the work is choosing the right code for the result you want.

How do I convert a number to text in Excel?

TEXT is the most direct route: =TEXT(A2,"0") strips formatting and returns the number as plain text with no decimals. Format Cells only changes how a number displays, it doesn't actually convert the underlying value to text, so a formula referencing that cell will still treat it as a number. Text to Columns with a "Text" column format is the other real conversion method, useful for converting an entire column at once without writing a formula.

What is the difference between TEXT and VALUE functions in Excel?

TEXT and VALUE do opposite jobs. TEXT takes a number and returns a formatted text string; VALUE takes a text string that looks like a number and converts it back into an actual number Excel can calculate with. If you've used TEXT to build a display string and later need to extract a number from it, VALUE is the function that reverses it.

How do I format a date using the TEXT function?

Use a date format code as the second argument: =TEXT(A2,"mm/dd/yyyy") for a numeric date, =TEXT(A2,"mmmm d, yyyy") for a spelled-out version like "July 13, 2026," or =TEXT(A2,"dddd") to return just the weekday name. The code determines the layout entirely; the underlying date value in A2 never changes.

Why can't I use TEXT output in calculations?

Because it isn't a number anymore. TEXT returns a string, and functions built to work with numbers, like SUM or AVERAGE, treat a TEXT result the same way they'd treat any other text: ignored, not converted into the calculation. That's often more dangerous than an outright error, since the formula still returns a value, just a quietly wrong one. If a formula needs both a calculated value and a formatted display, keep them separate: apply number formatting to the cell for display, and reserve TEXT for cases where you're building a text string on purpose.

Does TEXT work with negative numbers?

Yes, and you can control exactly how negatives display by adding a second section to the format code, separated by a semicolon. "0.00;-0.00" displays negatives with a leading minus sign, while "0.00;(0.00)" wraps them in parentheses instead. A third section after another semicolon controls how zero displays, and a fourth controls text values.

Related Functions

Function	Use this when...
`VALUE`	You need to reverse TEXT and convert a formatted text string back into a real number for calculations.
`TEXTJOIN`	You're combining multiple TEXT results, or other strings, with a delimiter instead of chaining `&` operators.