TEXT converts numbers and dates into formatted text. Here is everything you need — format codes, 6 real examples, and common mistakes.
TEXT converts a number or date into formatted text. You control exactly how it looks — as currency, a date in long format, a percentage, a padded code, or any custom pattern. The result is text, not a number, so you can combine it with other text using &.
TEXT is essential for building dynamic labels, creating formatted references, and displaying numbers in reports exactly as you need them.
=TEXT(value, format_text)| Argument | Description |
|---|---|
| value required | The number, date, or cell reference to format. |
| format_text required | The format code as a text string in quotes. Uses the same codes as Excel cell formatting. |
| Format code | Example input | Result |
|---|---|---|
| "£#,##0.00" | 1234.5 | £1,234.50 |
| "$#,##0" | 1234.5 | $1,235 |
| "0.00%" | 0.156 | 15.60% |
| "dd/mm/yyyy" | Date serial | 14/06/2026 |
| "d mmmm yyyy" | Date serial | 14 June 2026 |
| "mmmm" | Date serial | June |
| "dddd" | Date serial | Sunday |
| "00000" | 42 | 00042 |
| "#,##0" | 1234567 | 1,234,567 |
| "0.0" | 3.14159 | 3.1 |
=TEXT(B2, "$#,##0.00")Converts 1234.5 to "$1,234.50". The # shows digits, the 0 forces zeros, and commas add thousands separators.
="Report prepared on "&TEXT(TODAY(), "d mmmm yyyy")Produces: "Report prepared on 14 June 2026". Without TEXT, joining TODAY() with text would produce an ugly serial number like 46356.
=TEXT(B2/C2, "0.0%")Converts a decimal like 0.156 to "15.6%". Useful when you want the % symbol included in a text label.
=TEXT(A2, "00000")Converts 42 to "00042" — a five-character padded code. The number of zeros sets the total length. Essential for product codes, reference numbers and IDs.
=TEXT(A2, "mmmm")Returns "June" from a date in A2. Use "mmm" for the short form "Jun". Useful for grouping and labelling charts.
="INV-"&TEXT(A2, "0000")&"-"&TEXT(TODAY(), "yyyy")Produces "INV-0042-2026" — a formatted invoice reference from a raw number and today's year.
TEXT converts numbers to text strings. The result looks like a number but cannot be used in SUM, AVERAGE, or arithmetic. If you need to calculate with it, keep the original number and only use TEXT for display.
=TEXT(A2, dd/mm/yyyy) errors. The format code must always be in quotes: =TEXT(A2, "dd/mm/yyyy").
Using "£" in the format code hardcodes the pound sign. Use "$" for dollars. For locale-dependent currency, format the cell instead of using TEXT.
ExcelPro has TEXT formula exercises across all tracks — free to start, no download needed.
Try TEXT exercises →