fx
FunctionPedia
Quick Answer

TEXT Function

Formats a number or date as text using a format code.

✓ Excel✓ Google SheetsExcel All versions

Syntax

TEXT
(value, format_text)

Parameters

ParameterDescriptionRequired
valueThe numeric value, date, time, or cell reference to format.Required
format_textA text string enclosed in quotes that specifies the number format, e.g. "0.0%" or "yyyy-mm-dd".Required

Basic Example

Show a fraction as a percent

=TEXT(0.25,"0.0%")
Result25.0%

0.25 is formatted with one decimal of percent precision, producing the text string "25.0%".

Advanced Examples

Example 1: Format a date

Building a label from a date cell A1 containing 2024-01-15

Convert a date into a readable text label

=TEXT(A1,"yyyy-mm-dd")
Result: 2024-01-15
The date serial is rendered as a fixed-width text string using the year-month-day pattern.

Example 2: Thousands separator with currency

Display 1234567 as money

Apply a thousands and currency format

=TEXT(1234567,"$#,##0.00")
Result: $1,234,567.00
The #,##0 grouping adds thousand separators and the currency symbol is prepended.

How TEXT Works

TEXT takes a numeric value and applies the formatting codes from format_text (which mirror custom number formats). The result is always a text string, not a number, so it can no longer be used directly in math without conversion back via VALUE.

1
Pick the value
Select the number or date cell you want to format.
2
Choose a format code
Write the format code in double quotes, e.g. "0.00%".
3
Wrap with TEXT
Enter =TEXT(value, "format") and press Enter.

Important Notes & Limitations

  • The output is text, so it cannot be used directly in arithmetic without converting back with VALUE.

  • Format codes are locale dependent (decimal and thousands separators differ by region).

  • TEXT cannot reformat an already-text string; it only formats underlying numeric/date values.

Common Errors & Fixes

#VALUE! errorThe format_text argument is missing or is not a valid text string.

Fix: Provide a quoted format code such as "0.0%".

Unexpected outputForgetting quotes around the format code turns it into a defined name or range.

Fix: Always enclose the format code in double quotes.

Download Practice File

Practice TEXT with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the TEXT function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Frequently Asked Questions

Why does TEXT return text instead of a number?
Its purpose is to produce a formatted display string, so the result is text even though the input was a number.
Can I use TEXT on a date?
Yes, supply a date format code such as "mm/dd/yyyy" and the underlying date serial is rendered as text.
How do I convert the text back to a number?
Wrap the result with VALUE, e.g. =VALUE(TEXT(0.25,"0.0%")).