Excel TEXT Function (Format Value as Text) – Examples & Practice
Practice the Excel TEXT function online with an interactive grid, instant feedback, and clear formula help.
Instruction
Format the date/value in A2 as text using pattern "yyyy-mm-dd".
Formula Syntax
=TEXT(value, format_text)
- value: A number, date, or time to format.
- format_text: Format code in quotes, like "yyyy-mm-dd" or "0.00".
What it does
TEXT formats a numeric value (including dates/times) as text using a format code. It is ideal when you need formatted output inside other text formulas.
Excel TEXT Function Examples
ISO-like date string
=TEXT(A2, "yyyy-mm-dd")
Turns a date serial into a fixed-width text date.
Currency-like display
=TEXT(B2, "$#,##0.00")
Creates a text representation with currency style (locale-dependent).
date-calendar.xlsx
| A | B | |
|---|---|---|
| 1 | Date | Offset Months |
| 2 | 2026-01-15 | 0 |
| 3 | 2026-02-28 | 1 |
| 4 | 2026-03-31 | -1 |
| 5 | 2026-07-04 | 2 |
| 6 | 2026-12-20 | 3 |
| 7 | Output |
Input Formula
Need Help?
Tips
- Prototype codes using Format Cells UI, then copy patterns.
- Keep a cheat sheet of your team’s standard codes.
- Avoid TEXT in the only column you need to sort numerically.
TEXT Function Use Cases
- Concatenate dates cleanly in messages
- Export-friendly fixed formats
- Dashboard labels that combine numbers and words
- Normalize keys for matching
- Teaching format codes vs formatting
Common mistakes - TEXT function not working
- Using TEXT then expecting numeric math without VALUE
- Locale-sensitive symbols copied between regions
- Overly complex format codes without comments
- TEXT on blank cells without IF
- Confusing displayed text with underlying serials
FAQ
Does TEXT change the underlying value?
TEXT returns a string for display or downstream text operations; the original cell is unchanged.
Where do I find format codes?
Excel format code rules mirror custom number formats; start from Format Cells examples.
TEXT vs custom number format?
Use TEXT when you need the formatted result inside other text formulas.
Can TEXT break sorting?
Yes, because the result is text; keep a numeric column when sorting is required.
Why is my TEXT output wrong?
Usually a mismatched format code or a non-date input.
Comparison
| Approach | Result type |
|---|---|
| TEXT | String |
| Cell format | Display only |
| VALUE | String to number |
Example
=TEXT(A2,"yyyy-mm-dd")
Advanced examples
TEXT in messages
="As of "&TEXT(TODAY(),"yyyy-mm-dd") builds audit-friendly headers.