Ravindra BagaleCourses & study guides मराठी Track your progress

3. Formulas and Functions

3.8 Joining Text: &, CONCAT, TEXTJOIN and TEXT

By Last updated:

Quick answer: the short, self-contained version of this lesson is the guide Excel CONCAT, TEXTJOIN and & to Join Text.

Joining text with & and TEXT with example — in 30 seconds

What: The & sign joins pieces of text. TEXT turns a number into text with the format you choose.

Why: WhatsApp updates and report titles need one readable line, like "BLK-1007 - Solapur - ₹1,299".

Tiny example: =A2&" - "&C2&" - ₹"&TEXT(G2,"#,##0") turns three cells into one line. Without TEXT, ₹1,299 would show as 1299.

First look at the data (before → after):

Joining text with & and TEXT with example: before and after tables, fictional FreshGo Maharashtra data

Expected result — One readable line per order

J: Message
BLK-1001 - Pune - ₹64
BLK-1002 - Nashik - ₹270
BLK-1007 - Solapur - ₹1,299

About this data: Teaching sample for a fictional shop “Orbit Logistics”. Figures and names (e.g. Priya Deshmukh) are made up for this lesson on joining text and concat textjoin and text.

Before table and formula as text

Before — Three separate cells

A: Order ID C: City G: Amount
BLK-1001 Pune 64
BLK-1002 Nashik 270
BLK-1007 Solapur 1299

About this data: Demo table only — not real customer or company data. Built to practise joining text and concat textjoin and text.

Formula / click path

J2: =A2&" - "&C2&" - ₹"&TEXT(G2,"#,##0")

Do it step by step:

  1. Click J2.
  2. Type =A2&" - "&C2&" - ₹"&TEXT(G2,"#,##0").
  3. Every text piece, including spaces and the ₹ sign, goes inside double quotes.
  4. Press Enter and copy down.
  5. Check BLK-1007: the amount shows with a comma, ₹1,299.
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

About this data: Made-up rows so you can copy the pattern for joining text and concat textjoin and text. Currencies may use ₹, £, € or $.

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. ="FreshGo sales till "&TEXT(TODAY(),"dd-mm-yyyy")&": ₹"&TEXT(SUM(G2:G11),"#,##0") → FreshGo 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.

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).

Loading practice tasks...

  1. ID and city (Easy): Join A2 and C2 with " - ".
  2. CONCAT two cells (Easy): Join A3, a space, and C3 with CONCAT.
  3. Courier: route label (Easy): Join Shipments!B2, " to " and Shipments!C2.
  4. List of cities (Medium): Join C2:C4 with ", " in between.
  5. Amount with comma (Medium): Show G8 as 1,299 using TEXT.
  6. Courier: list of AWBs (Medium): Join Shipments!A2:A4 with ", ".
  7. Courier: weight label (Medium): Show Shipments!D4 as 0.75 kg.
  8. Full order label (Hard): Make BLK-1007 - Solapur - ₹1,299 from A8, C8 and G8.
  9. Date as text (Hard): Show the date in B2 as 02-11-2026 using TEXT.
  10. Courier: SMS line (Hard): Make: AWB-7001 ships on 10-11-2026 from Shipments!A2 and Shipments!E2.

See all practice tasks in the Task Hub →

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Joining Text And Concat Textjoin And Text?
Card 1: What is Joining Text And Concat Textjoin And Text? · Download PNG
Revision card 2: Tiny example — Joining Text And Concat Textjoin And Text
Card 2: Tiny example — Joining Text And Concat Textjoin And Text · Download PNG
Revision card 3: Checklist — Joining Text And Concat Textjoin And Text
Card 3: Checklist — Joining Text And Concat Textjoin And Text · Download PNG