Formatting numbers and dates with TEXT
Write messages that show a date or an amount nicely.
TEXT turns a number into text in the format you give it, like "#,##0.00".
Build a message for each invoice in D2:D7, like Total £1,234.50: the text "Total £" followed by the amount in the format #,##0.00. Write one formula in D2 and copy it down.
Typing formulas is easiest on a computer with a keyboard.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Invoice | Amount | Due date | Message | |
| 2 | INV-2401 | 1614.5 | 29 Apr 2026 | ||
| 3 | INV-2403 | 579.5 | 17 Apr 2026 | ||
| 4 | INV-2411 | 963.25 | 4 Apr 2026 | ||
| 5 | INV-2414 | 3253 | 6 Mar 2026 | ||
| 6 | INV-2419 | 490.5 | 11 May 2026 | ||
| 7 | INV-2427 | 516.25 | 5 Apr 2026 | ||
| 8 | |||||
| 9 | |||||
| 10 | |||||
| 11 | |||||
| 12 |
How TEXT works
Why does my date turn into a number?
A date is stored as a number (see How Excel stores dates). Join it to text with & and you get the number:
="Due "&C2shows "Due 46096". Numbers lose their formatting too: 1234.5 instead of 1,234.50.What does
TEXTdo?It turns a number or date into text, formatted the way you say:
=TEXT(C2,"d mmm yyyy")gives "15 Mar 2026". Use it inside a joined message:="Due "&TEXT(C2,"d mmm yyyy").Which format codes are there?
For numbers:
0always shows a digit,#only when needed,,adds thousands separators and.00two decimals:"#,##0.00"gives 1,234.50."0%"shows 0.25 as 25%. For dates:dfor the day (ddadds a leading zero: 06),mmmfor Jan,mmmmfor January,yyyyfor the year, andddddfor the day of the week.Can I still do sums with the result?
No:
TEXTgives text, not a number. Keep the real number in its own cell for calculations, and useTEXTonly for labels and messages.
Know someone fighting with a spreadsheet? Send them this page.