Course / Working with Text

Formatting numbers in text with TEXT

Build labels like "Total: $1,234.50" and "Due Mar 21, 2025" that keep their formatting. Intermediate · 5 min · TEXT, Format codes, Dates

When you join a number to text with &, Excel uses the raw value and ignores the cell's formatting. $1,234.50 turns into 1234.5, and a date turns into a serial number like 45737.

TEXT(value, format_text) fixes that. It formats a number as text, using the same codes as the cell number formats:

• "$#,##0.00" gives $1,234.50
• "0%" gives 15%
• "mmm d, yyyy" gives Mar 21, 2025
• "dddd" gives the day of the week, such as Friday

Steps

1. First, see the problem. In D2, join a label to the amount exactly like this: ="Total: "&B2

2. Fix it with TEXT. Change D2 to ="Total: "&TEXT(B2, "$#,##0.00") and copy it down to D7.

3. Do the same for the due dates. In E2:E7, build labels like Due Mar 21, 2025 using the format "mmm d, yyyy".

4. TEXT can format the result of any calculation. In B9, build the line Outstanding: $10,185.54 by wrapping a SUM of the amounts in TEXT.

5. Last one. In B10, show the earliest due date with its weekday, like Next due: Friday, Mar 7. Use MIN to find the earliest date, and the format "dddd, mmm d".

Remember

• & uses a cell's raw value, not its formatting.
• TEXT(value, "format") turns numbers and dates into neatly formatted text.
• Common codes: "$#,##0.00", "0%", "mmm d, yyyy" and "dddd".
• The result is text, so use it for labels and messages rather than further maths.

Previous: Cleaning up messy text · Next: How Excel stores dates