Ravindra BagaleCourses & study guides

5. Data Cleaning A–Z

5.4 Inconsistent Case

By Last updated:

Before

Customer Product
sHRADDHA bAGALE AMUL BUTTER 100 G
ruhi bagale gokul cow milk 500 ml
ZOYA chitale bhakarwadi 250 g

After

Customer Product
Shraddha Bagale Amul Butter 100 g
Ruhi Bagale Gokul Cow Milk 500 ml
Zoya Chitale Bhakarwadi 250 g

Steps in Excel

  1. Names: =PROPER(TRIM(A2)).
  2. Codes that must be capitals (Store ID, PAN-style codes): =UPPER(TRIM(A2)).
  3. E-mails: =LOWER(TRIM(A2)) (5.11).
  4. PROPER also capitalises units ("500 Ml", "100 G"). Fix them with SUBSTITUTE: =SUBSTITUTE(SUBSTITUTE(PROPER(TRIM(B2))," Ml"," ml")," G"," g"), or use Flash Fill on a few examples.

Ravindra Bagale's Tip

Excel che lookups aani COUNTIF case-insensitive aahet, mhanun khup students case kade durlaksha kartat – pan report madhe "PUNE" aani "Pune" vegle distat aani Power Query/Power BI madhe te vegle hou shakta. Case ekdach standard kara. PROPER nantar units aani "McD" sarkhe apvad ekda najretun ghala.

Practice task

Standardise 15 customer names to proper case and 15 store IDs to upper case. Fix units like "Ml" and "Kg" after PROPER.