TEXT
Formats a number and converts it to text.
=TEXT(value, format_text)This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.
What the function does
Overview
Formats a number and converts it to text.
The TEXT function converts a numeric value into a text string according to a specified format mask. This is a critical tool for data preparation and reporting because it allows numbers, dates, and times to be concatenated with other text strings while maintaining their visual formatting (e.g., currency symbols, comma separators, or leading zeros). By default, concatenating a date like 2023-01-01 with a string results in a raw serial number like '44927'; using TEXT(A2, "yyyy-mm-dd") preserves the intended display. Best practices include using it for fixed-width padding and standardizing regional date formats. Note that the output of TEXT is always a string, meaning it cannot be used in mathematical operations without first being converted back to a number (using VALUE or a double unary operator). In some regional versions of Excel, format codes may vary (e.g., using 'j' for year instead of 'y' in certain European locales).
Quick reference
Syntax
=TEXT(value, format_text)Inputs
Arguments
Example: A2
Example: "mm/dd/yyyy"
Formula patterns
Examples
Pad Numbers with Leading Zeros
=TEXT(A2, "00000")Preserve Date Format in Concatenation
="Submission Date: " & TEXT(B2, "dd-mmm-yyyy")Display Large Numbers as Millions
=TEXT(C2, "#,#.0,, \"M\"")Avoid these issues
Common Errors
#VALUE!
Cause: The format_text argument is longer than 255 characters or contains an invalid format mask that Excel cannot parse.
Fix: Ensure the format string is enclosed in double quotes and does not exceed the character limit.
Literal Format Code
Cause: The format_text argument was not enclosed in double quotes, causing Excel to treat it as a named range or error.
Fix: Always wrap the second argument in double quotes, for example: TEXT(A2, "$#,##0") instead of TEXT(A2, $#,##0).
Platform support
Compatibility
Source: Microsoft Support
Common questions
Frequently Asked Questions
Formats a number and converts it to text.
value: Numeric value to format ext: Format code (in quotes)
#VALUE!: Ensure the format string is enclosed in double quotes and does not exceed the character limit. Literal Format Code: Always wrap the second argument in double quotes, for example: TEXT(A2, "#,##0") instead of TEXT(A2,#,##0).