TEXT

Formatting numbers and dates with TEXT

Spreadsheet in your browser
Lesson concept What you'll do
1 / 5

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".

Typing formulas is easiest on a computer with a keyboard.

A1
ABCDE
1InvoiceAmountDue dateMessage
2INV-24011614.529 Apr 2026
3INV-2403579.517 Apr 2026
4INV-2411963.254 Apr 2026
5INV-241432536 Mar 2026
6INV-2419490.511 May 2026
7INV-2427516.255 Apr 2026
8
9
10
11
12
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,"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: 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 for the day (dd adds a leading zero: 06), 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.