Excel TEXT Function Format Codes Explained

Formulas & functions · Updated November 18, 2026

Excel TEXT format codes are patterns you pass to the TEXT function to turn a number, date, time, or percentage into formatted text. For example, =TEXT(A2,"0.00") displays the value in A2 with two decimal places; the format code is the quoted string "0.00".

What the TEXT function does

TEXT converts a value to text using a number-format pattern. That is different from applying a number format to a cell: ordinary cell formatting changes how a value looks while keeping it numeric, whereas TEXT returns characters. A result produced by TEXT is therefore usually not suitable for arithmetic until it is converted back to a number.

That distinction matters when preparing labels, reports, and messages. A value such as 0.25 can remain a number and display as 25% through cell formatting, or a formula can turn it into the text “25%” for use in a sentence. TEXT is especially useful when a formula needs to combine a value with words, because concatenation otherwise may show the underlying number rather than the intended display.

With a percentage stored in A2 as 0.25, the formula =TEXT(A2,"0%") returns the text 25%. If A2 contains 1234.5 and the workbook uses US English separators, =TEXT(A2,"$#,##0.00") returns $1,234.50. In both cases the format code is the second argument, inside quotation marks.

Required, optional, and alignment digit placeholders

The most important numeric codes are 0, #, and ?. They all represent digit positions, but they handle missing digits differently:

  • 0 requires a digit. If there is no digit at that position, Excel displays zero. Use it for fixed decimal places or padded identifiers.
  • # displays a digit when one is present but does not add insignificant zeros. It is useful when a decimal fraction should not show trailing zeros.
  • ? behaves like an optional digit but reserves blank space for a missing digit. That can line up decimal points or fractions in a column.

For a simple example, with the number 27 in A2, =TEXT(A2,"00000") returns 00027. This can create a fixed-width display, but it does not turn the original value into a text identifier in the cell; the formula result itself is text. If a number is an account code or product ID, decide whether it should be stored as text from the start, particularly if it may contain letters or must never be used in calculations.

With 12.5 in A2, the code 0.00 shows two decimal places, while 0.## shows up to two and omits unnecessary trailing zeros. A format code controls display, not rounding of the stored source value. TEXT may display a rounded version, but the original number remains unchanged in its source cell.

Decimal points, grouping, percentages, and scaling

A period in a format code marks the decimal position, and a comma placed between digit placeholders requests grouping separators. For example, #,##0.00 requests grouped whole-number digits and two decimal places. The actual displayed separator characters can depend on Excel’s regional settings: in some locales a comma is used for decimals and a period for grouping.

A percent sign tells Excel to display a value as a percentage by multiplying its displayed value by 100. Thus 0.25 appears as 25% with the code 0%, and as 25.0% with 0.0%. Do not multiply the source by 100 as well unless that is actually intended, or the displayed result will be 100 times too large.

Commas at the end of a number pattern scale the displayed number by thousands. For instance, one trailing comma in #,##0, displays 1,234,567 as 1,235; two trailing commas in #,##0,, display it as 1. This is a display convention, not a change to the underlying value. It is useful for compact summaries, but include a label such as “millions” when the scale might otherwise be unclear.

Scientific notation uses E to introduce the exponent. A pattern such as 0.00E+00 displays 1234.5 as approximately 1.23E+03. The number of zeros after the decimal controls the displayed precision, while the zeros after the exponent sign set the minimum number of exponent digits.

Date and time format codes

Excel stores dates as serial numbers and times as fractions of a day. Date and time codes make those values readable. Common date components include d for day, m for month, and y for year. Repeating a letter changes the display: dd can add a leading zero to a day, mmmm spells out the month, and yy shows a two-digit year.

With a real Excel date representing November 2, 2026, in A2, =TEXT(A2,"dddd, mmmm d, yyyy") returns Monday, November 2, 2026. The weekday and month names follow the language and regional settings used by Excel. If A2 contains text that only looks like a date, Excel may not interpret it as the intended serial date, so check the source value before troubleshooting the format code.

Time codes include h for hour, m for minute, and s for second. The letter m is context-sensitive: next to an hour or before seconds, it is read as minutes; in a date pattern, it normally means month. A pattern such as h:mm displays an hour and minutes, while h:mm AM/PM requests a 12-hour clock.

For durations longer than 24 hours, square brackets matter. The code [h]:mm displays total elapsed hours instead of restarting the hour count after a day. This is useful for a duration such as 27 hours, which should display as 27:00 rather than 3:00. A standard clock format is appropriate for a time of day; a bracketed format is usually better for elapsed time.

Text, literal characters, and multiple sections

To include a character that would otherwise be interpreted as part of a format code, enclose literal text in double quotation marks or escape a character with a backslash. For example, 0.00 "kg" appends the unit kg after a number. In a TEXT formula, the quotation marks that delimit the Excel format string are also part of the formula, so literal quotation marks inside that string need Excel’s usual escaping rules. Keep units in the format only when the output is meant for display; a text result with a unit is not a numeric measurement.

Custom number formats can have up to four sections separated by semicolons. In order, these apply to positive numbers, negative numbers, zero, and text. A pattern such as 0.0;(0.0);-;@ can show positive and negative values differently, display a dash for zero, and use the text placeholder for text. The @ placeholder represents text in custom cell formats. For TEXT, remember that the purpose is to return a text result, so test the exact input and format combination when adapting a multi-section pattern.

Some formats can also include color names in square brackets, such as [Red], or conditions such as [>100]. These are advanced custom-format features; they affect how a value is presented rather than changing its value. Avoid adding complexity unnecessarily. If a result needs different wording based on a condition, an IF formula is often clearer than trying to encode all the logic in a format string.

Choosing between TEXT and cell formatting

Use cell formatting when the value must remain usable as a number or date. Select the cells and use Home > Number Format to choose a built-in format, or open the Format Cells dialog with Ctrl+1 on Windows and use its Number tab. On Mac, use Command+1 to open Format Cells. A custom format can also be entered in the dialog’s Custom category. These settings change the displayed appearance without replacing the value.

Use TEXT when the formatted result must become part of a text string. For example, with a date in A2 that represents November 2, 2026, ="Report date: "&TEXT(A2,"mmmm d, yyyy") returns Report date: November 2, 2026. The TEXT call supplies the display pattern before concatenation. Without it, a date included in a text expression may appear as its serial number.

For a final report, another option is often to keep the date or number in its own cell and format that cell, rather than combining everything into a single text string. Separate values are easier to sort, filter, recalculate, and reuse. TEXT is best when a finished label is genuinely needed, not as a replacement for sound data layout.

Common problems and how to avoid them

  • The result is text, not a number. This is expected. If later calculations are needed, keep the numeric source separate instead of calculating from the TEXT result.
  • A date displays as a number or an unexpected date. Confirm that the source cell contains a valid date value, not ambiguous text, and remember that Excel date interpretation can vary with regional settings.
  • Separators or currency symbols differ. Number-format syntax and displayed punctuation can be influenced by locale. A pattern written with US-style commas and periods may display differently on a system with other regional conventions.
  • Zeros or decimal places appear unexpectedly. Check whether the code uses required zeros (0) or optional digits (#), and count the decimal placeholders.
  • A percent is 100 times too large. The percent format already scales the displayed value by 100. Check whether the source value was multiplied earlier.
  • A duration wraps after 24 hours. Use an elapsed-time code such as [h]:mm rather than a clock-time hour code.

One further caution concerns identifiers with leading zeros. If an ID such as 0042 is stored as the number 42, TEXT can display the missing zeros, but the result is still text. If data is imported or exchanged with another system, consider setting the destination column to text before importing, so Excel does not interpret the identifier as an ordinary number.

Frequently Asked Questions

What does 0 mean in an Excel format code?
A zero is a required digit placeholder. If the number does not have a digit in that position, Excel displays a zero there, which is why 00000 pads 27 as 00027.

What is the difference between 0 and # in Excel format codes?
0 forces a digit, including a zero when needed. # displays a digit only when one exists, so it can suppress leading or trailing zeros that are not significant to the chosen pattern.

Does the TEXT function change the original number?
No. It returns a formatted text result and leaves the source value unchanged. The distinction is that the result of TEXT is text, even when its characters look like a number or date.

How do I show a date as a month name?
Use a month code such as mmmm for a full month name or mmm for an abbreviated one. The source needs to be a valid Excel date, and the language of the displayed name can depend on regional settings.

Can I use a TEXT format code to show more than 24 hours?
Yes. Use square brackets around the hour code, as in [h]:mm, to show total elapsed hours rather than a clock time that cycles after 24 hours.

Should I use TEXT or format the cell?
Format the cell if the value needs to remain numeric or date-like for calculations, sorting, or filtering. Use TEXT when the formatted value must be inserted into a text label or sentence.

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.