The TEXT function
Writing a number or a date in the form you want, inside a text: "Invoice of 15/03/2024", "Total: $1,234.50".
Syntax
=TEXT(value, format_text)
In a French Excel: =TEXTE(valeur; format_texte).
Arguments
- value: the number or date.
- format_text: the format code, in quotes:
"dd/mm/yyyy","0.00","000000".
An example
="Invoice of "&TEXT(B2,"dd/mm/yyyy")
Without TEXT, the date would show as a number (45366).
Pitfalls
- Codes depend on Excel's language:
"dd/mm/yyyy"in English,"jj/mm/aaaa"in French. A formula copied from one Excel to the other shows the code instead of the date. - The result is text: it no longer calculates.
Going further
- Translate a formula: translates the formula and flags format codes to check.
- Leading zeros and long numbers: keep zeros with no formula.
Frequently asked questions
- Why does TEXT show "aaaa" instead of the year?
- The code was written for a French Excel: in an English Excel, the year is "yyyy".