Excel TEXT Function
last modified October 5, 2026
The TEXT function converts a numeric value to text and formats it
with a number format of your choice. It is the standard tool for embedding
formatted numbers, dates, and times inside text strings. This tutorial
provides a comprehensive guide to using the TEXT function with
detailed examples. You'll learn the syntax, the most useful format codes, and
practical applications to master this essential Excel function.
TEXT Function Basics
The TEXT function takes a value and a format code and returns the
value as formatted text. Cell formatting only changes how a value is
displayed, while TEXT performs a real conversion: the result is a
text string that looks like the formatted number. The function is available in
every version of Excel.
| Component | Description |
|---|---|
| Function Name | TEXT |
| Syntax | =TEXT(value, format_text) |
| Arguments | value - the number to format; format_text - the format code |
| Return Value | Text string (always text, never a number) |
This table breaks down the essential components of the TEXT
function. It shows the function name, basic syntax format, arguments, and
return value characteristics.
| Argument | Meaning |
|---|---|
| value | The numeric value to convert. It can be a literal number, a date or
time, a cell reference, or an expression that returns a number, such as
A1*B1. A value that is already text is passed through
unchanged. |
| format_text | The format code, enclosed in double quotes, such as "0.00" or
"yyyy-mm-dd". It accepts the same codes as custom number formats. An
unquoted format code causes a #NAME? error. |
TEXT always returns text, even when the result looks like a
number. The string "1234.57" cannot be used directly in calculations; either
keep the original numeric cell or convert the text back with
VALUE.
Format Code Reference
The format_text argument uses the same codes as custom number
formats. The tables below list the most common codes for numbers, dates, and
times.
| Code | Purpose | Example |
|---|---|---|
| 0 | Digit placeholder; forces zeros | =TEXT(5.5, "0.00") returns "5.50" |
| # | Digit placeholder; drops extra zeros | =TEXT(5.5, "#.##") returns "5.5" |
| ? | Digit placeholder; space for insignificant zeros | =TEXT(5.5, "0.0?") returns "5.5 " with a trailing space |
| , | Thousands separator | =TEXT(1234, "#,##0") returns "1,234" |
| % | Multiplies by 100 and adds a percent sign | =TEXT(0.85, "0%") returns "85%" |
| E+ | Scientific notation | =TEXT(1234.567, "0.00E+00") returns "1.23E+03" |
| $ and other symbols | Literal characters, shown as typed | =TEXT(1234.567, "$#,##0.00") returns "$1,234.57" |
| @ | Inserts the value as text | =TEXT(123, "@") returns "123" |
Characters such as + - ( ) : / and the space are also treated as
literals and appear in the output exactly as typed. The next table shows the
date codes applied to the serial number 45292, which is January 1, 2024.
| Date code | Purpose | Result for 45292 |
|---|---|---|
| yyyy | Four-digit year | "2024" |
| yy | Two-digit year | "24" |
| mmmm | Full month name | "January" |
| mmm | Abbreviated month name | "Jan" |
| mm | Two-digit month number | "01" |
| m | One- or two-digit month number | "1" |
| dddd | Full weekday name | "Monday" |
| ddd | Abbreviated weekday name | "Mon" |
| dd | Two-digit day of month | "01" |
| d | One- or two-digit day of month | "1" |
Codes combine freely: =TEXT(45292, "yyyy-mm-dd") returns
"2024-01-01". The next table shows time codes applied to the value 0.75, which
is 18:00.
| Time code | Purpose | Result for 0.75 |
|---|---|---|
| h:mm | 24-hour time | "18:00" |
| h:mm AM/PM | 12-hour time with marker | "6:00 PM" |
| hh:mm AM/PM | 12-hour time, padded hour | "06:00 PM" |
| h:mm:ss AM/PM | 12-hour time with seconds | "6:00:00 PM" |
| ss | Two-digit seconds | "00" |
Without AM/PM in the format, Excel uses the 24-hour clock; adding
it switches to a 12-hour clock with the AM or PM marker. Keep in mind that m
means minutes only when it follows an hour code or precedes the seconds code; a
standalone m is interpreted as a month.
Format codes are locale-dependent on non-English systems. In German Excel, t stands for day (Tag) and j for year (Jahr); French uses j (jour) and a (an). Month and weekday names and the AM/PM markers come from the system locale, and many locales use a semicolon as the argument separator. The examples in this tutorial assume English (US) Excel.
Basic TEXT Example
This example demonstrates the simplest use of the TEXT function, rounding a number to two decimal places.
=TEXT(1234.567, "0.00")
This formula converts the number 1234.567 to the text "1234.57". The two zeros force exactly two decimal places, and the value is rounded to match, the same way the ROUND function rounds. The result is text, not a number.
TEXT with Thousands Separator
The comma code inserts thousands separators and keeps large numbers readable. This example shows the same format applied to positive and negative values.
| A | B |
|---|---|
| 1234 | =TEXT(A1, "#,##0") |
| -1234 | =TEXT(A2, "#,##0") |
The table shows values in column A and a TEXT formula in column B that adds thousands separators.
=TEXT(A1, "#,##0")
The formula returns "1,234" for A1 and "-1,234" for A2. The #,##0 code shows at least one digit and groups every three digits. Without an explicit negative section, Excel simply prepends a minus sign to negative results.
TEXT with Percent
Percent formats multiply the value by 100 and append a percent sign. This example converts decimals to percentages.
| A | B |
|---|---|
| 0.85 | =TEXT(A1, "0.0%") |
| 0.285 | =TEXT(A2, "0.0%") |
The table demonstrates converting decimals to percentages with one decimal place. The TEXT function applies the percentage format while converting the values to text.
=TEXT(A1, "0.0%")
The formula converts 0.85 to "85.0%" and 0.285 to "28.5%". The % code multiplies by 100 and adds a percent sign, while the 0.0 part keeps one decimal and rounds as needed. With the "0%" code, the value 0.537 returns "54%", since 53.7% rounds to 54%.
TEXT with Currency
Currency formats combine a literal symbol with digit placeholders. This example formats a number as US dollars.
=TEXT(1234.567, "$#,##0.00")
This formula converts 1234.567 to "$1,234.57". The dollar sign and the comma are literal characters shown as typed, while #,##0.00 controls the digits. Any symbol can be embedded this way, for example "€#,##0.00" for euros. The result resembles the DOLLAR function, but with full control over the format.
TEXT with Scientific Notation
Scientific notation is useful for very large or very small numbers. This example shows the E+ code.
=TEXT(1234.567, "0.00E+00")
This formula returns "1.23E+03", meaning 1.23 times 10 to the third power. The 0.00 part controls the mantissa digits, and the 00 after E pads the exponent to two digits with a sign.
Digit Placeholders: 0, #, and ?
The three digit placeholders differ in how they treat insignificant zeros. Choosing the right one controls padding and alignment.
| Formula | Result | Explanation |
|---|---|---|
| =TEXT(5.5, "0.00") | 5.50 | 0 forces both decimal digits |
| =TEXT(5.5, "#.##") | 5.5 | # shows digits only when needed |
| =TEXT(5.5, "0.0?") | 5.5 (trailing space) | ? keeps a space for the missing digit |
| =TEXT(42, "00000") | 00042 | Zeros pad the value to five digits |
The ? placeholder reserves room for a digit but displays a space, which aligns decimal points in a column. Zeros in the format also pad values with leading zeros: "00000" turns 42 into "00042", a common trick for IDs and product codes. A value with more digits than the format is never truncated; extra digits are kept.
TEXT with Date Formatting
Dates are stored as serial numbers, and TEXT is the standard way to turn them into readable date strings. This example formats a date in cell A1.
| A | B |
|---|---|
| 1/15/2023 | =TEXT(A1, "mmmm d, yyyy") |
The table shows a date in cell A1 and a TEXT formula in B1 that reformats the date. The format code specifies the full month name, day, and four-digit year.
=TEXT(A1, "mmmm d, yyyy")
This formula converts the date in A1 to "January 15, 2023". Dates can also be
supplied as serial numbers: =TEXT(45292, "yyyy-mm-dd") returns
"2024-01-01", and the ddd and dddd codes extract the weekday, returning "Mon"
and "Monday" for the same date.
Concatenating a raw date discards its formatting. ="Due: "&A1 returns "Due: 44941", because the underlying serial number is joined, not the displayed date. Wrapping the cell in TEXT fixes this: ="Due: "&TEXT(A1, "mmmm d, yyyy") returns "Due: January 15, 2023", regardless of how A1 is formatted.
TEXT with Time Formatting
Excel stores times as fractions of a day: 0.75 is 18:00, or 6:00 PM. This example shows how to display such a value in different ways.
| Formula | Result |
|---|---|
| =TEXT(0.75, "h:mm") | 18:00 |
| =TEXT(0.75, "h:mm AM/PM") | 6:00 PM |
| =TEXT(0.75, "hh:mm AM/PM") | 06:00 PM |
| =TEXT(0.75, "h:mm:ss AM/PM") | 6:00:00 PM |
The h code omits the leading zero while hh keeps it, and each format turns the fraction of a day into a readable clock time. These codes work on any time value, whether typed as a decimal like 0.75 or as a full time such as 6:00 PM.
TEXT with Conditional Sections
A format code can hold up to four sections separated by semicolons: positive;negative;zero;text. You can use this with TEXT to produce sign-specific output, such as accounting-style negatives.
| A | B |
|---|---|
| 1234.5 | =TEXT(A1, "#,##0.00;(#,##0.00);-") |
| -1234.5 | =TEXT(A2, "#,##0.00;(#,##0.00);-") |
| 0 | =TEXT(A3, "#,##0.00;(#,##0.00);-") |
=TEXT(A1, "#,##0.00;(#,##0.00);-")
The formula returns "1,234.50" for the positive value, "(1,234.50)" for the negative one, and "-" for zero. With only two sections, the first handles positive numbers and zeros, and the second handles negatives. Color codes such as [Red] and [Green] are accepted in format codes, but they only affect cell display; TEXT never applies color to the string it returns.
TEXT vs VALUE Round Trip
Because TEXT returns text, its result cannot be used in arithmetic until it is converted back to a number. The VALUE function parses formatted text and returns the underlying number when it recognizes the format.
| Formula | Result |
|---|---|
| =VALUE(TEXT(1234.567, "0.00")) | 1234.57 |
| =VALUE(TEXT(0.85, "0.0%")) | 0.85 |
| =VALUE(TEXT(45292, "yyyy-mm-dd")) | 45292 |
| =VALUE("$1,234.57") | 1234.57 |
| =VALUE("Monday") | #VALUE! |
VALUE recognizes currency symbols, thousands separators, and percent signs, but
round-tripping through TEXT loses any precision the format did not show:
VALUE(TEXT(1234.567, "0")) returns 1235, not 1234.567. A double unary like
=--TEXT(A1, "0.00") converts numeric text as well. Text that is not a
recognized number, such as "Monday", causes a #VALUE! error.
TEXT vs Cell Formatting
Applying a number format to a cell and using TEXT with the same code produce the same display, but they differ in what happens underneath.
| Aspect | Cell formatting | TEXT function |
|---|---|---|
| Underlying value | Stays numeric | Converted to text |
| Use in calculations | Fully supported | Requires VALUE conversion |
| Concatenation | Formatting is ignored | Formatted string is joined |
| Sorting and lookups | Sorts as a number | Sorts as text |
As a rule, keep numbers numeric with cell formatting and reach for
TEXT when a formatted value must live inside a text string, such
as a report label, an email body, or a lookup key built from a date.
TEXT Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Format code not enclosed in quotes | #NAME? error | Write the format as text: "0.00" |
| Result used directly in arithmetic | #VALUE! error or wrong totals | Convert back with VALUE or keep the numeric cell |
| Date concatenated without TEXT | Serial number such as 44941 appears | Wrap the date in TEXT with a date format |
| Format codes from another locale | #VALUE! error or garbled dates | Use the codes of your locale (t, j) and its argument separator |
| Referenced cell is empty | "0.00" or a similar formatted zero | Empty cells count as zero; guard with IF when needed |
| Unexpected rounding | "1235" instead of "1234.567" | TEXT rounds to the digits the format shows |
| Source cell contains an error | The same error is passed through | TEXT cannot mask #N/A or #DIV/0!; fix the source |
Most TEXT problems reduce to two habits: always quote the format code, and always remember the output is text. Checking these first, along with the locale of the format codes, resolves the majority of issues.
The TEXT function is the bridge between numeric values and
readable text in Excel. It formats numbers, currencies, percentages, scientific
notation, dates, and times with the same codes used by custom number formats.
Remember that TEXT always returns text: cell formatting preserves the number
for calculations, while TEXT performs a real conversion that must be reversed
with VALUE before the value can be used in arithmetic. Mastering the format
codes will let you produce polished, consistent output in reports, dashboards,
and any strings that combine text with numbers.
Author
List all Excel Formulas.