3.8 Joining Text: &, CONCAT, TEXTJOIN and TEXT
| Method | Example | Result |
|---|---|---|
& |
=C2&", "&"Maharashtra" |
Pune, Maharashtra |
CONCAT(text1, …) (Excel 2019+) |
=CONCAT(A2,"-",C2) |
BLK-1001-Pune |
TEXTJOIN(delimiter, ignore_empty, text1, …) (Excel 2019+) |
=TEXTJOIN(", ",TRUE,"Kothrud","","Baner") |
Kothrud, Baner |
TEXT(value, format_text) |
=TEXT(B2,"dd-mm-yyyy") |
02-11-2026 |
When you join a number or date with text, Excel loses the format: ="Order date: "&B2 gives Order date: 46328. Wrap it in TEXT:
="Order date: "&TEXT(B2,"dd-mmm-yyyy")&" | Amount: ₹"&TEXT(G2,"#,##0")
→ Order date: 02-Nov-2026 | Amount: ₹64
Worked example – dashboard title. ="Blinkit sales till "&TEXT(TODAY(),"dd-mm-yyyy")&": ₹"&TEXT(SUM(G2:G11),"#,##0") → Blinkit sales till 25-09-2026: ₹2,870 (the date part changes daily). Microsoft 365 users can also list all Pune areas in one cell: =TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Stores[Area],Stores[City]="Pune"))).
Ravindra Bagale's Tip
When you join a date or an amount into text, a strange number like 46328 appears – many students get confused. When joining a number or date, always format it with the TEXT function. And instead of CONCATENATE (the old text-joining function), use the newer CONCAT or TEXTJOIN.
Ravindra Bagale's Tip – मराठी
Date किंवा amount text मध्ये जोडलं की 46328 सारखा विचित्र number येतो – बरेच students confuse होतात. Number/date जोडताना नेहमी TEXT function ने format द्या. आणि CONCATENATE (जुनं, मजकूर जोडण्याचं function) ऐवजी नवीन CONCAT किंवा TEXTJOIN वापरा.
Ravindra Bagale's Tip – हिंदी
Date या amount को text में जोड़ते ही 46328 जैसा अजीब number आ जाता है – बहुत से students confuse हो जाते हैं. Number/date जोड़ते समय हमेशा TEXT function से format दो. और CONCATENATE (पुराना, text जोड़ने वाला function) की जगह नया CONCAT या TEXTJOIN इस्तेमाल करो.
Practice task
Create a column "Summary" like BLK-1001 | Pune | ₹64 | 02-11-2026. Then, in one cell, list all distinct categories separated by commas (Microsoft 365).