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 |
About this data: Teaching sample for a fictional shop “HomeNest Supplies”. Figures and names (e.g. Neha Shah) are made up for this lesson on inconsistent case.
After
| Customer | Product |
|---|---|
| Shraddha Bagale | Amul Butter 100 g |
| Ruhi Bagale | Gokul Cow Milk 500 ml |
| Zoya | Chitale Bhakarwadi 250 g |
About this data: Demo table only — not real customer or company data. Built to practise inconsistent case.
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.
Loading practice tasks...
- Proper case name (Easy): Show B2 with each word starting with a capital.
- Code in capitals (Easy): Show A2 in capitals.
- Small letters (Easy): Show C2 in small letters.
- Brand in proper case (Easy): Show C7 in proper case.
- Trim and proper (Medium): Clean B3 (spaces and case).
- Trim and upper (Medium): Clean A5 into capitals.
- Clean product name (Medium): Clean B5 (spaces and case).
- Size in small letters (Medium): Show D3 in small letters.
- Fix a unit after PROPER (Hard): Make D3 read "200 g" using PROPER and SUBSTITUTE.
- Shelf label (Hard): Make "Lindt Dark Chocolate" from C3 and B3.
- Fix ml (Hard): Make D5 read "500 ml".
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
