TEXT(value, format) turns a number or date into formatted text — essential when you join values into labels or messages.
In this article
Dates
| Format | Result for 17-Sep-2019 |
|---|---|
"dd-mmm-yyyy" |
17-Sep-2019 |
"dddd" |
Tuesday |
"ddd" |
Tue |
"mmmm yyyy" |
September 2019 |
"yyyymmdd" |
20190917 (sortable, for file names) |
"\Qq" with a month number |
use ="Q"&ROUNDUP(MONTH(A2)/3,0) instead |
Numbers
| Format | 1234567.8 becomes |
|---|---|
"#,##0" |
1,234,568 |
"[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0" |
12,34,568 (Indian lakh style) |
"0.00" |
1234567.80 |
"0.0,,\M" |
1.2M |
"00000" on 42 |
00042 (leading zeros) |
"0%" on 0.256 |
26% |
Joining into sentences
="Sales on "&TEXT(A2,"dd-mmm")&" were ₹"&TEXT(B2,"#,##0")Without TEXT you would get Sales on 43725 were 1234567.8.
⚠️ TEXT returns text, not a number — you cannot SUM the result. Keep the original numbers for calculations and use TEXT only for labels.
💡 Format codes depend on regional settings in some versions (for example “jjjj” for year in French Excel). For shared international files, prefer cell formatting over TEXT.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong