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
Date kiwa amount text madhe jodla ki 46328 sarkha vichitra number yeto – khup students confuse hotat. Number/date jodtana nehmi TEXT function ne format dya. Ani CONCATENATE (जुने – मजकूर जोडण्याचे फंक्शन) aivaji navin CONCAT kiwa TEXTJOIN vapra.
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).