TEXT in Excel: Format Numbers, Dates & Currency
Converts a value to text in a specific number format.
How to Format Numbers and Dates with TEXT in Excel
The TEXT function converts a number into formatted text using custom format codes.
Common Format Codes
| Goal | Formula | Result |
|---|---|---|
| Date Format | =TEXT(TODAY(), "yyyy-mm-dd") | 2026-08-26 |
| Currency | =TEXT(1234.5, "$#,##0.00") | $1,234.50 |
| Leading Zeros | =TEXT(42, "00000") | 00042 |
Common Errors & Fixes
Result shows #####
Causes:- Column too narrow for the formatted text output.
- Custom format code is invalid or mistyped.
Fixes:- Widen the column.
- Double-check the format string syntax — it must be in quotes.
Frequently Asked Questions
What is the most useful TEXT format code?
"$#,##0.00" for currency, "YYYY-MM-DD" for dates, "0.0%" for percentages, and "00000" for leading zeros (like ZIP codes).
Why is my formatted number not calculating in formulas?
TEXT converts numbers to text, which other formulas may not recognize as numeric. Use the original cell for calculations and TEXT only for display/output.
Can TEXT format dates in Google Sheets?
Yes. TEXT works identically in Google Sheets. Format codes are the same: "MM/DD/YYYY", "DDD" for abbreviated day name, "MMMM" for full month name.
Related Formulas
Explore related formula generators to solve similar problems
🛠️ Related Tools
Want to become an Excel Pro?
Stop searching for formulas. Master Excel in 30 days with this top-rated course.
Learn More