Ravindra BagaleCourses & study guides

5. Data Cleaning A–Z

5.8 Splitting Columns

By Last updated:

Before

Location
Kothrud, Pune, 411038
College Road, Nashik, 422005
Dharampeth, Nagpur, 440010

After

Area City Pincode
Kothrud Pune 411038
College Road Nashik 422005
Dharampeth Nagpur 440010

Steps in Excel – three ways

  1. Text to Columns: insert two empty columns to the right › select the column › Data › Data Tools › Text to Columns › Delimited › tick Comma (and Treat consecutive delimiters as one) › in step 3 set Pincode to Text if you want to keep it as a code › Finish. Then TRIM the leading spaces.
  2. Flash Fill: type Kothrud in B2 › Ctrl + E; Pune in C2 › Ctrl + E; 411038 in D2 › Ctrl + E.
  3. TEXTSPLIT (Microsoft 365 / Excel 2024): =TRIM(TEXTSPLIT(A2,",")) spills the three parts.

Pincodes shown are fictional-style examples for the areas.

Ravindra Bagale's Tip

Text to Columns ujvikadchya columns var overwrite karto – khup students cha shejarcha data asa nahisa hoto. Split karaychya aadhi ujvikade purese rikame columns insert kara. Ani comma nantar space rahate, mhanun split nantar TRIM visru naka.

Practice task

Split "Area, City, Pincode" for ten stores using all three methods. Compare which one updates automatically when the source changes.