5.9 Merging Columns
Before
| Area | City | Platform |
|---|---|---|
| Baner | Pune | FreshGo |
| Tarabai Park | Kolhapur | MarketRush |
| Nirala Bazar | Sambhaji Nagar | FreshGo |
About this data: Teaching sample for a fictional shop “Skyline Clinics”. Figures and names (e.g. Sana Sheikh) are made up for this lesson on merging columns.
After
| Store Label |
|---|
| FreshGo – Baner, Pune |
| MarketRush – Tarabai Park, Kolhapur |
| FreshGo – Nirala Bazar, Sambhaji Nagar |
About this data: Demo table only — not real customer or company data. Built to practise merging columns.
Steps in Excel
=C2&" – "&A2&", "&B2- Or
=TEXTJOIN(", ",TRUE,A2,B2)for "Area, City" skipping blanks (Excel 2019+). - Or Flash Fill: type the first label › Ctrl + E.
- Keep the original columns; the merged label is for display. Never use Merge & Center to "merge columns" – it keeps only the top-left value.
Ravindra Bagale's Tip
Many students think "merge" means Merge & Center – but that keeps only one cell and deletes the rest of the data! To join columns, use & or TEXTJOIN. And if you will use the merged label as a key, join the TRIMmed values, otherwise the lookup won't match.
Ravindra Bagale's Tip – मराठी
बरेच students "merge" म्हणजे Merge & Center समजतात – ते फक्त एक cell ठेवतं आणि बाकी data delete करतं! Columns जोडण्यासाठी & किंवा TEXTJOIN वापरा. आणि merged label key म्हणून वापरणार असाल तर TRIM केलेल्या values जोडा, नाहीतर lookup match होत नाही.
Ravindra Bagale's Tip – हिंदी
बहुत से students "merge" का मतलब Merge & Center समझते हैं – वह सिर्फ़ एक cell रखता है और बाकी data delete कर देता है! Columns जोड़ने के लिए & या TEXTJOIN इस्तेमाल करो. और merged label को key बनाना हो तो TRIM की हुई values जोड़ो, वरना lookup match नहीं होगा.
Practice task
Create a "Store Label" column for all stores. Then create a unique key StoreID|Date for each order row, e.g. =G2&"|"&TEXT(B2,"yyyymmdd").
Loading practice tasks...
- Full name (Easy): Join A2 and B2 with a space.
- Surname first (Easy): Make "Walker, James" from row 3.
- Name with room (Easy): Make "Riya - 101" from A2 and C2.
- CONCAT (Easy): Join A4, a space and B4 with CONCAT.
- Name in capitals (Medium): Make "MATEO LOPEZ" from row 5.
- Name and country (Medium): Make "Sara Haddad (UAE)".
- TEXTJOIN a row (Medium): Join A2:C2 with " | ".
- All first names (Medium): Join A2:A6 with ", ".
- Booking code with date (Hard): Make "101-20261220" from C2 and D2.
- Arrival message (Hard): Make "Ankit Jain arrives on 24-12-2026 for 5 nights".
- Initials (Hard): Make "J.W." from row 3.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
