The Excel TEXT function is an indispensable asset whenever you want to display numerical or time data in a readable and customized format. Whether it’s to transform a raw date into more user-friendly text or to add leading zeros to a number, TEXT allows you to go far beyond the standard display. In this article, we will review the syntax, the different format codes, and several practical applications to fully leverage this function.
What is the TEXT function?
The TEXT function takes two arguments: the value to convert and the format mask. It always returns a string, even if the input is numeric or a date. The benefit? Precisely controlling the appearance of data without altering its origin, which is particularly useful for:
- Standardizing display in reports or dashboards.
- Preparing labels for mail merges or tags.
- Associating a defined format with an intermediate calculation.
Syntax and format codes
Basic syntax
The function syntax is as follows:
=TEXT(value; “format”)
Where:
- value can be a cell reference, a calculation, or a date.
- format is a string enclosed in quotes, composed of formatting codes: digits, separators, literal text.
Main codes for dates and times
Here are the most commonly used codes for dates:
| Code | Meaning |
|---|---|
| dd | Day with two digits (01 to 31) |
| d | Day without leading zero (1 to 31) |
| mm | Month with two digits (01 to 12) |
| mmm | Abbreviated month (Jan, Feb, …) |
| yyyy | Year with four digits |
| yy | Year with two digits |
| hh | Hours (00 to 23) |
| mm | Minutes (00 to 59) – beware of conflict with month |
| ss | Seconds (00 to 59) |
For example, =TEXT(A2;”dd mmm yyyy”) transforms 44197 into “15 Mar 2021” if A2 contains an Excel date.
Codes for numbers
For numbers, use:
- 0: mandatory digit (replaces with 0 if no value).
- #: optional digit (appears only if necessary).
- ,: thousands separator.
- .: decimal separator.
- “text”: displays literal text.
For example:
=TEXT(B2;”0 000.00 €”) to get the amount 1 234.50 € when B2 equals 1234.5.
Practical examples and tips
1. Converting dates to a more explicit label
If you want to write the days of the week in a tracking table, combine the code jjjj with your date:
- =TEXT(C3;”dddd”) → converts
03/06/2022into Friday.
This method is more flexible than using an auxiliary column + INDEX or VLOOKUP to display the day name. It remains fully dynamic during filtering or sorting.
2. Prefixing product codes or numbers
To maintain a fixed length, for example 8-digit references:
- =TEXT(D2,”00000000″) → displays 00001234 when D2 is 1234.
This technique integrates perfectly into Excel documents that will be exported as CSV or sent by email, as the guaranteed number of zeros facilitates reading or importing into another system.
3. Display decimals without rounding
Unlike the “Decimals” option which rounds, TEXT will truncate according to the format:
- =TEXT(E2,”#.##”) on 3.14159 gives 3.14.
If you want to force the display of n decimals, even if they are zeros, use 0 instead of #.
Comparison with other Excel functions
While LEFT, RIGHT and MID extract or cut parts of text, the TEXT function affects the appearance of numbers and dates. It does not modify the raw value, only its display format.
To assemble several pieces (text, result of TEXT, etc.), use the && symbol or the CONCATENATE function. Example:
=TEXT(F2,”dd/mm/yyyy”) & ” – Order no.” & TEXT(G2,”0000″)
Summary table of common formats
| Purpose | Formula | Result |
|---|---|---|
| Long date | =TEXT(A2,”dddd dd mmmm yyyy”) | Wednesday 15 September 2021 |
| 24-hour time | =TEXT(A2,”hh:mm:ss”) | 14:05:09 |
| 6-digit number | =TEXT(B2,”000000″) | 001234 |
| Monetary | =TEXT(B2,”#,##0.00 €”) | 1,234.50 € |
| Percentage | =TEXT(C2,”0.00%”) | 12.34% |
Advanced use cases
In a dashboard where dates, times and numbers are combined, using TEXT allows standardizing the display when exporting to PDF. You can also combine this function with slicers to dynamically filter periods: slicer labels will display more readable date formats.
Finally, in the context of mail merge, if you work on a Word database connected to an Excel file, the column formatted by TEXT ensures that dates and amounts keep their appearance when merging into letters.
Best practices and pitfalls to avoid
- Be careful with the use of
mmfor month and minutes; prefer the code[$-fr-FR]mmif necessary. - Know that a TEXT result cannot be used directly in a calculation; you must keep the original value in another cell.
- For exports, remember to test on different software (OpenOffice, Google Sheets) if compatibility is required.
- Do not overload your formulas: for very customized formats, consider using an auxiliary column or a VBA macro.
FAQ
- 1. Why do I get #VALUE! with TEXT?
- Make sure the first argument is a numeric value or a date recognized by Excel. Non-convertible text triggers the error.
- 2. Can TEXT be used to change the currency?
- Yes, by adding the currency symbol in the format string (e.g.
"#,##0.00 €"). It is also possible to use your local format. - 3. How to display the month name in French?
- Use
mmmormmmmafter setting the language (e.g.[$-F800]mmmmfor a full month). - 4. Does TEXT work on Excel Online?
- Yes, the function is available on all recent versions, including Excel 365 and Excel Online.