Excel TEXT Function Format Options and Examples Explained

Formulas & functions · Updated November 18, 2026

Excel’s TEXT function converts a number, date, or time into text displayed with a format code: use =TEXT(value, format_text). Its options include codes for decimals, thousands separators, percentages, dates, times, and custom layouts; the right code depends on how you want the result to look.

What the TEXT function does

TEXT changes how a value is represented in a formula result, not the underlying value in its original cell. The function returns text, which is useful when you want to combine a formatted number with words, or create a consistent label. For example, a value of 0.875 can be displayed as 87.5% and joined to the word complete.

This conversion has an important trade-off: the result is text rather than a number. Excel cannot use that text result as an ordinary numeric value in calculations in the same way it can use the original number. Keep the original value in a separate cell if you need to calculate with it, and use TEXT only for the displayed result.

If you only need to change how a cell looks, without making a text string, format the cell instead. Number formatting changes the display while preserving the numeric value, so the cell can still be used directly in calculations.

Syntax and the format_text argument

The function has two arguments: value is the number, date, or time to display, and format_text is the format code enclosed in quotation marks. The code resembles the custom number formats available in Excel’s cell-formatting options. It is not a descriptive name such as Currency or Date; it is a pattern made from characters such as 0, #, %, and m.

With the number 12.5 in A2, =TEXT(A2,"0.00") returns the text 12.50. The two zeroes after the decimal point require two displayed decimal places, so Excel adds a trailing zero. If the formula is entered in another cell, the original A2 value remains 12.5.

In Microsoft 365 for Windows, formula argument separators are normally commas, as shown here. Some regional settings use semicolons between arguments; if Excel rejects a formula copied from another locale, the argument separator may be the reason. Format-code punctuation and displayed decimal or thousands separators can also vary with regional settings.

Number format codes for decimals and digits

The three most useful digit placeholders are 0, #, and ?. A zero displays a digit even when that position has no digit in the value. A hash sign displays a digit only when one is present. A question mark reserves space for a missing digit, which can help align decimal points in a column.

  • 0 requires a digit in that position, so it can add leading or trailing zeroes.
  • # allows a digit to appear but does not force a zero into an empty position.
  • ? reserves a blank space for an absent digit, useful for visual alignment.

With 12.5 in A2, =TEXT(A2,"0.00") returns 12.50, while =TEXT(A2,"#.##") returns 12.5. The first pattern fixes two decimal places; the second permits up to two without forcing the final zero. A pattern such as 00000 can preserve leading zeroes in an identifier: with 731 in A2, =TEXT(A2,"00000") returns 00731. Treat such identifiers as text afterward; the added zeroes do not make the underlying number longer.

A comma can mark thousands grouping, as in #,##0. With 12500 in A2, =TEXT(A2,"#,##0") returns 12,500 in a locale that uses a comma for grouping. A comma placed at the end of a number format can instead scale the displayed value by a thousand, so placement matters. For scientific notation, a pattern such as 0.00E+00 displays a number using a mantissa and exponent.

Percentages, currency, and other number styles

A percent sign in a format code displays a value multiplied by 100 and adds the percent symbol. It does not merely attach a sign to the existing digits. With 0.125 in A2, =TEXT(A2,"0.0%") returns 12.5%. Applying that code to 12.5 would display 1250.0%, because Excel interprets 12.5 as twelve and a half, not as 12.5 percent.

Currency and accounting-style output can be built from a currency symbol and digit placeholders. With 1234.5 in A2, =TEXT(A2,"$#,##0.00") returns $1,234.50 under typical English-language settings. The dollar sign here is a displayed symbol; it does not convert a value between currencies or identify a particular exchange rate. For a symbol that must be treated literally or for a longer label, place the literal text in quotation marks inside the format code, taking care to double the quotation marks required by the formula.

Custom formats can also show negative values differently from positive values or display a chosen marker for zero. Number formats can have sections separated by semicolons, conventionally for positive numbers, negative numbers, zero, and text. For instance, a number pattern can put negative values in parentheses and show a dash for zero. Because such patterns are easy to misread, test them with positive, negative, and zero examples before using them in a report.

Date and time format options

Excel stores dates and times as numbers, and TEXT can display those values as readable calendar dates or clock times. Common date codes include d for day, m for month, and y for year. Repeating a letter changes the level of detail: ddd gives an abbreviated weekday, dddd gives its full name, mmm gives an abbreviated month, and mmmm gives the full month name.

With a date in A2 equal to March 8, 2025, =TEXT(A2,"dddd, mmmm d, yyyy") returns Saturday, March 8, 2025. The displayed language and punctuation can depend on Excel’s regional settings. A cell that looks like a date but is actually stored as text may not behave like a date value in this formula; convert or correct the source data if the result is unexpected.

Time codes use h for hour, m for minute, and s for second. The letter m can mean month or minute, depending on context: near hour or second codes it is generally read as minutes, while in a date pattern it means month. For a time value of 14:05 in A2, =TEXT(A2,"h:mm AM/PM") returns 2:05 PM. To show accumulated hours above 24, an elapsed-time code such as [h]:mm is useful; without brackets, hours ordinarily wrap around the clock.

Combining formatted values with text

One of the main reasons to use TEXT is to make a sentence or label from a value without displaying an unwanted raw number. With 0.875 in A2, =TEXT(A2,"0.0%")&" complete" returns 87.5% complete. The ampersand joins the text pieces. The result is still a text string, so it is suitable for a heading or message rather than a calculation.

The same approach works with dates. With a valid date in A2, a format code can produce a month name or a compact date label before joining it to other words. Choose a format that remains clear when the result is read on its own: abbreviations may save space, but a full month name can be less ambiguous in a report shared across regions.

For a worksheet, dashboard, or chart label that needs to remain numeric, consider whether a cell number format can do the job instead. A formatted cell can show a currency value or percentage while still supplying its numeric value to formulas and charts. Use concatenation with TEXT when the combined wording is the point of the output.

Choosing TEXT or ordinary cell formatting

Use TEXT when a formula must return a formatted string, such as a date embedded in a sentence, a percentage followed by a label, or an identifier with fixed-width zeroes. Use ordinary number formatting when you want a number to look different but remain a number. This distinction matters in totals, sorting, charting, and later formulas.

  • Choose cell formatting for numeric tables, calculations, and charts where the stored value should stay numeric.
  • Choose TEXT for formula-generated captions and sentences where formatted digits must sit beside other text.
  • Keep a separate numeric source when a formatted text result also needs to support calculations.
  • For identifiers such as postal codes, consider storing the value as text from the outset if leading zeroes are meaningful.

The formula works in Microsoft 365 for Windows and the same TEXT function is available in Excel for Mac and Excel for the web. The main differences relevant here are in the interface and regional settings, not the function’s basic purpose. On Windows, Ctrl+1 opens Format Cells for ordinary cell formatting; on Mac, the equivalent shortcut is Command+1. The function itself does not require opening that dialog.

Common problems and how to fix them

A common surprise is that the result will not sum as expected. That is usually because TEXT returned characters rather than a numeric value. Use the original numeric cells in calculations, or remove the conversion where the output needs to remain numeric. Converting the displayed text back to a number is not a reliable substitute for keeping the original value, especially when the text includes currency symbols or localized separators.

Another frequent issue is a date displayed as a serial number or an unexpected result from a date code. Check that the source cell contains a real Excel date or time, not a string that merely resembles one. Also check the pattern: a single m may indicate month or minute depending on surrounding codes, and a time over 24 hours needs an elapsed-time pattern if the total hours should not wrap.

If the output has too many or too few digits, inspect the placeholders. A zero forces a position to display; a hash sign does not. If a formula shows a formula error, verify that the format code is enclosed in quotation marks and that the formula’s parentheses and argument separator match your Excel settings. If a currency or date appears differently on another computer, regional settings may control the displayed symbol, language, or separators. For consistent published output, test the workbook in the intended locale.

Finally, do not use TEXT as a way to change the actual precision of a number. A pattern that displays one decimal place rounds the representation for display, but it does not alter the original value. If the underlying number must be rounded for a calculation, use a rounding function separately and choose it deliberately.

Frequently Asked Questions

What is the syntax for the Excel TEXT function?
The syntax is =TEXT(value, format_text). The first argument is the value to display, and the second is a quoted format code such as "0.00" or "mmm d, yyyy".

How do I add leading zeroes with TEXT?
Use zero placeholders for the desired total width. For example, with 731 in A2, =TEXT(A2,"00000") returns the text 00731. The result is text, not a number padded with extra stored digits.

Why does TEXT turn my number into text?
That is the function’s purpose: it returns a formatted text string. If you need the value to remain numeric for calculations or charting, apply a cell number format instead of wrapping it in TEXT.

How do I format a date with TEXT?
Use date codes in the second argument, for example "mmmm d, yyyy" for a full month name, day, and four-digit year. The source must be a valid Excel date value for predictable results.

Can TEXT display a percentage?
Yes. Use a percent sign in the format code, such as "0.0%". Excel multiplies the displayed value by 100, so 0.125 appears as 12.5% with that pattern.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.