Formatting numbers and dates with TEXT

1 / 4

Each invoice needs a message in D2:D7 with its amount, like "Total £1,614.50".

keyboard Typing formulas is easiest on a computer with a keyboard.

amount

message

Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.

Study notes

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 "&C2 shows "Due 46096". Numbers lose their formatting too: 1234.5 instead of 1,234.50.
What does TEXT do?

It turns a number or date into text, formatted the way you say: =TEXT(C2,"dd mmm yyyy") gives "15 Mar 2026". Use it inside a joined message: ="Due "&TEXT(C2,"dd mmm yyyy").
Which format codes are there?

For numbers: 0 always shows a digit, # only when needed, , adds thousands separators and .00 two decimals: "#,##0.00" gives 1,234.50. "0%" shows 0.25 as 25%. For dates: d or dd for the day, mmm for Jan, mmmm for January, yyyy for the year, and dddd for the day of the week.
Can I still do sums with the result?

No: TEXT gives text, not a number. Keep the real number in its own cell for calculations, and use TEXT only for labels and messages.

Know someone fighting with a spreadsheet? Send them this page.