IntermediateText
Syntax
=TEXT(value, format_text)| Argument | What it means |
|---|---|
value | The number or date. |
format_text | The format in quotes, such as "0.00", "#,##0", "0%" or "dd/mm/yyyy". |
Examples with results
Every result below is calculated by the practice engine on the sample data shown, not typed in by hand.
Examples that need no data
=TEXT(1234.5,"#,##0.00")Thousands separator and two decimals
=TEXT(0.256,"0%")A percentage
Office supplies sales (fictional)
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Item | Qty | Unit price | Region | Date |
| 2 | Pen | 10 | 1.5 | North | 2025-01-15 |
| 3 | Notebook | 5 | 12 | South | 2025-02-03 |
| 4 | Pen | 20 | 1.5 | South | 2025-02-28 |
| 5 | Stapler | 2 | 40 | North | 2025-03-10 |
| 6 | Folder | 15 | 3.25 | East | 2025-03-22 |
| 7 | Notebook | 8 | 12 | East | 2025-04-05 |
| 8 | Pen | 12 | 1.5 | North | 2025-04-18 |
| 9 | Marker | 30 | 2 | South | 2025-05-02 |
=TEXT(E2,"dd/mm/yyyy")A date in day/month/year order
=TEXT(E3,"mmm yyyy")Month and year
Try it yourself
Change the formula or switch to other sample data.
Press Calculate to see the result.
The practice box runs in your browser with BDK’s own calculator, which covers about 60 common functions. Cell references work as in Excel (B2, B2:B9). Your formulas are not saved or sent anywhere. For anything unusual, check the result in Excel itself.
How people use TEXT at work
- Finance and accounting
Put a formatted amount inside a sentence.
Data: Customer invoices (fictional)="Amount due: "&TEXT(C2,"#,##0.00") - Administration
A date inside a sentence.
Data: Customer invoices (fictional)="Invoice date: "&TEXT(D2,"dd/mm/yyyy")
Common mistakes and tips
- The result is text, so you cannot add it up afterwards.
- Format codes can depend on your language settings: some regions use semicolons or different date letters.
Works in
All versions of Excel, and Google Sheets.
Lessons that use it
- Work out commission from sales tiersIntermediate lesson, about 6 min
- Work out years of service and probation datesIntermediate lesson, about 6 min
- Find due dates and overdue invoicesIntermediate lesson, about 7 min
- Compare your budget with what you spentBeginner lesson, about 5 min
- Measure marketing campaign resultsIntermediate lesson, about 7 min
Need Something Different? Let’s Talk.
Looking for different features or something built around your workflow? Share your idea with us.