How to Use TEXT Function in Excel: Format Numbers as Text
Use the
TEXT function in Excel to convert numbers or dates into text with a specific format. The syntax is TEXT(value, format_text), where value is the number or date and format_text is the format pattern you want to apply.Syntax
The TEXT function has two parts:
- value: The number, date, or time you want to format.
- format_text: A text string that defines the format you want to apply, like date or number patterns.
This function returns the formatted value as text.
excel
TEXT(value, format_text)
Example
This example shows how to format a date and a number using the TEXT function.
excel
=TEXT(DATE(2024,6,15), "mmmm dd, yyyy") =TEXT(1234.567, "#,##0.00")
Output
June 15, 2024
1,234.57
Common Pitfalls
Common mistakes include:
- Using incorrect format codes (like "MM" for months instead of "mm" for minutes).
- Forgetting to put the format pattern inside quotes.
- Expecting the result to be a number;
TEXTalways returns text, which can affect calculations.
excel
=TEXT(0.5, mm/dd/yyyy) <em>Wrong: missing quotes around format</em> =TEXT(0.5, "mm/dd/yyyy") <em>Right: format in quotes</em>
Quick Reference
| Format Code | Meaning | Example |
|---|---|---|
| "0" | Digit placeholder (shows digit or zero) | TEXT(5, "00") → "05" |
| "#" | Digit placeholder (shows digit or nothing) | TEXT(5, "#") → "5" |
| "," | Thousands separator | TEXT(1234, "#,##0") → "1,234" |
| ".00" | Decimal places | TEXT(1.2, "0.00") → "1.20" |
| "mmmm" | Full month name | TEXT(DATE(2024,6,1), "mmmm") → "June" |
| "dd" | Day with leading zero | TEXT(DATE(2024,6,5), "dd") → "05" |
| "yyyy" | Four-digit year | TEXT(DATE(2024,6,5), "yyyy") → "2024" |
| "hh:mm AM/PM" | 12-hour time with AM/PM | TEXT(TIME(14,30,0), "hh:mm AM/PM") → "02:30 PM" |
Key Takeaways
The TEXT function converts numbers or dates to formatted text using a format pattern.
Always put the format pattern inside double quotes.
TEXT returns text, so results can't be used directly in calculations without conversion.
Use correct format codes to get the desired output (e.g., "mm" for minutes, "dd" for days).
The function is useful for displaying numbers and dates in a readable way inside formulas or reports.