📎 This article includes 1 downloadable practice file ↓
You join a date into a sentence, “Invoice dated ” & A2, and Excel prints “Invoice dated 46299”. Or a product code 00042 becomes 42. Or a report needs ₹12,34,568 in Indian commas. The TEXT function fixes all of these: it takes a number or date and returns it as text, formatted exactly how you say.
In this article
=TEXT(value, "format_code")The format code is the same language as Format Cells › Custom. Learn a handful of codes and you can format anything.
Dates
| Format code | 4 Oct 2026 shows as | Use it for |
|---|---|---|
dd-mmm-yyyy |
04-Oct-2026 | Unambiguous dates in letters and invoices |
dd/mm/yyyy |
04/10/2026 | Indian numeric date |
yyyy-mm-dd |
2026-10-04 | File names and text that must sort in date order |
dddd / ddd |
Sunday / Sun | Day name for rosters |
mmmm yyyy |
October 2026 | Report headings |
mmm-yy |
Oct-26 | Month labels in pivots and charts |
d mmmm, yyyy |
4 October, 2026 | Formal letters |
="Invoice dated " & TEXT(A2, "dd-mmm-yyyy")
="Report for " & TEXT(EOMONTH(TODAY(), -1), "mmmm yyyy") ' last month's nameTimes
| Code | 14:30 shows as |
|---|---|
hh:mm |
14:30 |
hh:mm AM/PM |
02:30 PM |
[h]:mm |
Total hours past 24, e.g. 38:15 for a timesheet total |
dd/mm/yyyy hh:mm |
04/10/2026 14:30 |
Numbers
| Code | Value | Result |
|---|---|---|
0.00 |
1234567.891 | 1234567.89 |
#,##0 |
1234567 | 1,234,567 (or 12,34,567 on a PC set to Indian format) |
00000 |
42 | 00042 (codes with leading zeros) |
0.0% |
0.1834 | 18.3% |
#,##0.00;(#,##0.00) |
-4520.5 | (4,520.50), accounting style negatives |
+0;-0;0 |
12 / -5 | +12 / -5, for “change vs last month” |
00000-00000 |
9876543210 | 98765-43210 |
Lakh and crore commas
Excel’s #,##0 follows the computer’s regional setting. If the file goes to people with different settings, force Indian grouping with this code:
=TEXT(A2, "[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0")1234567 becomes 12,34,567 and 125000000 becomes 12,50,00,000 on any machine. For “₹12.3 L” style labels, divide first:
=TEXT(A2/100000, "0.0") & " L"
=TEXT(A2/10000000, "0.00") & " Cr"Financial-year labels
="FY " & (YEAR(A2) - (MONTH(A2) < 4)) & "-" & TEXT(MOD(YEAR(A2) - (MONTH(A2) < 4) + 1, 100), "00")A date in May 2026 gives “FY 2026-27”; a date in February 2027 gives the same. The "00" keeps “27” two digits even for years like 2009-10.
TEXT vs cell formatting
Formatting a cell changes only how it looks; the value stays a number you can add up. TEXT creates real text, so it no longer sums and sorts differently. Rule of thumb: use cell formatting in tables, and TEXT only when you are joining values into a sentence, label, file name or key.
Where people go wrong
| Mistake | What happens | Fix |
|---|---|---|
| Joining a date without TEXT | “Due 46299” | Wrap the date: TEXT(A2,"dd-mmm-yyyy") |
| Using TEXT in a column you need to total | SUM returns 0 | Format the cell instead |
| Format codes in another language version | On some non-English Excel versions "yyyy" must be written as that language’s letters |
Test once on the user’s version, or use the date functions (YEAR, MONTH) to build labels |
| Comparing TEXT results to numbers | “00042” ≠ 42 | Compare like with like |
Practice
Download the practice workbook below. The Data sheet holds a date, amounts, a code, a time and a mobile number; you write the TEXT formula for each of 15 results. Each task has an automatic ✓ check, and the Answers sheet has a working formula if you get stuck.
Next: Excel date formulas cheat sheet.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗TEXT function practice workbook15 TEXT tasks (dates, lakh/crore commas, leading zeros, times, mobile numbers, FY labels) with automatic checks.⬇ XLSX · 14 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.