Ravindra BagaleCourses & study guides

5. Data Cleaning A–Z

5.9 Merging Columns

By Last updated:

Before

Area City Platform
Baner Pune Blinkit
Tarabai Park Kolhapur Amazon Now
Nirala Bazar Sambhaji Nagar Blinkit

After

Store Label
Blinkit – Baner, Pune
Amazon Now – Tarabai Park, Kolhapur
Blinkit – Nirala Bazar, Sambhaji Nagar

Steps in Excel

  1. =C2&" – "&A2&", "&B2
  2. Or =TEXTJOIN(", ",TRUE,A2,B2) for "Area, City" skipping blanks (Excel 2019+).
  3. Or Flash Fill: type the first label › Ctrl + E.
  4. 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

Khup students "merge" mhanje Merge & Center samajtat – to fakt ek cell thevto aani baaki data delete karto! Columns jodnyasathi & kiwa TEXTJOIN vapra. Ani merged label la key mhanun vaparnar asal tar TRIM kelelya values joda, nahitar lookup match hot nahi.

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