The TEXT function converts a number, date, time, or percentage into formatted text. It is useful when you want a value to keep a specific display format inside a sentence or combined text formula.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, Excel 2019, Excel for Mac, and Excel Online when cell formatting disappears after a number or date is joined with words.
Format a value with TEXT
The structure is TEXT(value, format_code).
- Click the cell where the formatted text should appear.
- Type =TEXT(.
- Choose the value or cell reference.
- Add a comma and the format code in quotation marks.
- Close the formula and press Enter.
- Example: =TEXT(A1,”mm/dd/yyyy”).
To combine formatted text with words, use ampersands. For example, =”Report generated on “&TEXT(A1,”mmmm d, yyyy”) creates a readable sentence using the date in A1.
Common TEXT format codes
These are useful starting points.
- “mm/dd/yyyy”: 06/10/2026
- “mmmm d, yyyy”: June 10, 2026
- “ddd, mmm d”: Wed, Jun 10
- “h:mm AM/PM”: 3:45 PM
- “0.00”: exactly two decimal places
- “$#,##0.00”: currency with commas and cents
- “0%”: decimal displayed as a percentage
- “#,##0”: whole number with thousands separators
More control
Remember the result is text
A TEXT result looks formatted, but it is no longer a true number or date for math. Keep source numeric cells available for calculations.
Build dynamic labels
A formula such as =”Total due: “&TEXT(SUM(B2:B10),”$#,##0.00″)&” by “&TEXT(C1,”mmmm d”) combines live calculations and dates into one sentence.
Test custom formats first
Use Format Cells > Number > Custom to experiment with number-format codes, then copy the working code into the TEXT formula in quotes.
Sources
- Microsoft Support – TEXT function (2025)
- Microsoft Support – Number format codes (2025)
- Microsoft Support – Combine text from two or more cells into one cell (2025)