5.4 Inconsistent Case
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
- Names:
=PROPER(TRIM(A2)). - Codes that must be capitals (Store ID, PAN-style codes):
=UPPER(TRIM(A2)). - E-mails:
=LOWER(TRIM(A2))(5.11). - 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 lookups and COUNTIF are case-insensitive, so many students ignore case – but in a report "PUNE" and "Pune" look different, and in Power Query/Power BI they can be treated as different. Standardise the case once. After PROPER, look over units and exceptions like "McD" once.
Ravindra Bagale's Tip – मराठी
Excel चे lookups आणि COUNTIF case-insensitive आहेत, म्हणून बरेच students case कडे दुर्लक्ष करतात – पण report मध्ये "PUNE" आणि "Pune" वेगळे दिसतात आणि Power Query/Power BI मध्ये ते वेगळे ठरू शकतात. Case एकदाच standard करा. PROPER नंतर units आणि "McD" सारखे अपवाद एकदा नजरेखालून घाला.
Ravindra Bagale's Tip – हिंदी
Excel के lookups और COUNTIF case-insensitive हैं, इसलिए बहुत से students case को नज़रअंदाज़ कर देते हैं – पर report में "PUNE" और "Pune" अलग दिखते हैं और Power Query/Power BI में ये अलग माने जा सकते हैं. Case एक ही बार standard कर दो. PROPER के बाद units और "McD" जैसे अपवाद एक बार ज़रूर देख लो.
Practice task
Standardise 15 customer names to proper case and 15 store IDs to upper case. Fix units like "Ml" and "Kg" after PROPER.