Excel TEXT Function
Chala mitrano — tEXT dresses a number as text. You pick the outfit with a format code.
चला मित्रांनो — TEXT संख्यांना text च्या वेषात आणते. Format code ने पोशाख निवडा.
चलो दोस्तों — TEXT संख्या को text के कपड़े पहनाता है. Format code से पोशाख चुनो.
Purpose
Convert a number or date into text using a format code you control — for labels, IDs, and printable reports.
Return value
Text that looks like the formatted number or date.
Syntax
Arguments
| Argument | Required? | Description |
|---|---|---|
value | Required | The number or date to format. |
format_text | Required | Format code in quotes, such as "0.00" or "dd-mmm-yyyy". |
Version
All Excel versions. LibreOffice Calc supports TEXT with similar format codes (some codes differ).
How to use — Why → What → How
Why: Nashik payroll builds 'Employee — ₹45,000' labels. London consulting prints 'Invoice date: 15-Oct-2026'. TEXT makes those printable strings. TEXT is for labels; keep numeric columns for maths. The SUM-on-text trap deserves a live fail demo every batch.
What: TEXT takes a value and a format code and returns text that looks formatted. Common codes: 0.00, #,##0, dd-mmm-yyyy, ₹#,##0. Read every argument in the table once before you type; each one changes the answer on purpose.
How — numbered steps:
- Type =TEXT(B2,"₹#,##0").
- For dates =TEXT(A2,"dd-mmm-yyyy").
- Join with CONCAT or & for full sentences.
- Do not SUM a TEXT column — convert back with VALUE if needed.
- Test format codes on one cell first.
Ravindra Bagale's Tip
Friends, once you TEXT a number it is text — you cannot SUM it until VALUE brings it back.
Ravindra Bagale's Tip – मराठी
मित्रांनो, TEXT केल्यानंतर ते text होते — SUM करायचे असेल तर VALUE ने परत आणा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, TEXT के बाद वह text है — SUM करना हो तो VALUE से वापस लाओ.
Ravindra Bagale's Tip
Friends, sleep on TEXT and rebuild example 1 tomorrow without looking. That five-minute test beats rereading the whole page.
Ravindra Bagale's Tip – मराठी
मित्रांनो, TEXT वर एक रात्र झोपा आणि उद्या उदाहरण 1 न पाहता पुन्हा लिहा. पाच मिनिटांची ही चाचणी पूरे पृष्ठ वाचण्यापेक्षा जास्त मदत करते.
Ravindra Bagale's Tip – हिंदी
दोस्तों, TEXT पर एक रात सोकर कल उदाहरण 1 बिना देखे फिर लिखो. पाँच मिनट का यह टेस्ट पूरा पेज पढ़ने से ज़्यादा मदद करता है.
Ravindra Bagale's Tip
Friends, after you finish TEXT, open yesterday’s real workbook and find one place this function would have saved ten minutes. That habit turns lessons into salary skills.
Ravindra Bagale's Tip – मराठी
मित्रांनो, TEXT शिकून झाल्यावर कालची खरी वर्कबुक उघडा आणि शोधा जिथे हे फंक्शन दहा मिनिटे वाचवू शकले असते. ही सवय धड्यांना पगाराची कौशल्ये बनवते.
Ravindra Bagale's Tip – हिंदी
दोस्तों, TEXT सीखने के बाद कल की असली वर्कबुक खोलो और ढूँढो जहाँ यह फ़ंक्शन दस मिनट बचा सकता था. यही आदत पाठ को सैलरी स्किल बनाती है.
Download practice file: practice-text.xlsx (includes sheet About this data, practice sheets, and Answers).
Worked examples
1) Rupee label
Nashik payroll.
| Name | Salary |
|---|---|
| Neha | 45000 |
| Omar | 52000 |
Result: ₹45,000
What it means: Salary as text with rupee style.
2) Date text
London invoice.
| Date |
|---|
| 2026-10-15 |
| 2026-11-01 |
Result: 15-Oct-2026
What it means: Readable date text.
3) Two decimals
Unit price.
| Price |
|---|
| 12.5 |
| 9 |
Result: 12.50
What it means: Always two decimals as text.
4) Join sentence
Slip line.
| Name | Salary |
|---|---|
| Neha | 45000 |
Result: Neha — ₹45,000
What it means: Ready for a printable slip.
5) Zero padding
Receipt numbers.
| Num |
|---|
| 42 |
| 7 |
Result: 00042
What it means: Five-digit style text.
6) Pound label
London fee.
| Fee |
|---|
| 1200 |
Result: £1,200
What it means: Pound-style text.
Common errors and fixes
Notes and tips
- TEXT returns text, not a number.
- Great for labels; keep raw numbers for maths.
- Date serials need a date format code.
- Payroll slips in Nashik and consulting PDFs in London both need pretty text. Teach TEXT beside CONCAT, and warn about SUM on text.
- TEXT vs cell format: cell format keeps the value numeric underneath; TEXT truly converts to text. TEXT vs VALUE: opposite directions.
- Fictional sample data only — practice amounts and names are made for learning.
- After the 10 tasks, re-read the errors table once.
- Say the business question in plain words before you type equals.
- Keep a scrap sheet for experiments so you do not overwrite class files.
- Fill formulas down only after the first row looks correct.
- When two answers look possible, write both on paper then test in Excel.
- TEXT is for labels; keep numeric columns for maths. The SUM-on-text trap deserves a live fail demo every batch.
- Revisit the TEXT errors table after the quiz — most classroom mistakes are listed there.
- Build one brand-new example on a blank sheet from memory before you close the laptop.
- If a result looks wrong, check spaces with LEN and case with exact equals before changing the formula.
- Share one tip from this page with a classmate in Marathi or Hindi — teaching locks the idea.
- Keep currency symbols consistent inside a single report column even when practice mixes ₹ $ £ € across examples.
- When Excel versions differ at home and in class, write the fallback formula under the modern one in a comment cell.
- Print the revision card for TEXT and stick it near your monitor for one week of real worksheets.
- When helping a junior, ask them to predict the result before pressing Enter — prediction builds judgement.
- Keep a personal notebook of the one mistake you make most often with this function; review it monthly.
In real work
Payroll slips in Nashik and consulting PDFs in London both need pretty text. Teach TEXT beside CONCAT, and warn about SUM on text. TEXT is for labels; keep numeric columns for maths. The SUM-on-text trap deserves a live fail demo every batch. Classroom drill: write three brand-new TEXT formulas on a blank sheet using names from your own city — if you can invent them without the page open, you own the skill. Then break one formula on purpose (wrong case, wrong start, extra space) and watch the error; recovering from that break is half of interview readiness.
How it compares
TEXT vs cell format: cell format keeps the value numeric underneath; TEXT truly converts to text. TEXT vs VALUE: opposite directions.
Interview corner
- Is TEXT numeric?
- No — text.
- SUM a TEXT column?
- Usually fails or ignores — keep numbers separate.
- Common date code?
- dd-mmm-yyyy.
Related functions
VALUE, CONCAT, TEXTJOIN, FIXED
Practice tasks (10)
- Task 1. TEXT salary as ₹#,##0.Hint: quotes around format.
- Task 2. Date as dd-mmm-yyyy.Hint: real date cell.
- Task 3. 0.00 on 12.5.Hint: two decimals.
- Task 4. Join name — salary sentence.Hint: & and TEXT.
- Task 5. Zero-pad 42 to 00042.Hint: 00000.
- Task 6. £ format for 1200.Hint: example 6.
- Task 7. Explain why SUM may fail.Hint: text vs number.
- Task 8. Keep raw salary column too.Hint: labels aside.
- Task 9. Fill formats for two staff.Hint: fill down.
- Task 10. Interview: numeric after TEXT?Hint: no.
Quick quiz
Answers: Q1=₹45,000 as text, Q2=Needs care / fails on pure text, Q3=dd-mmm-yyyy, Q4=VALUE, Q5=Quotes (check after you try).
Revision card
Standalone card (no tip or photo strip). QR opens this lesson. Download the PNG and revise offline.
