5.12 Pincodes and Codes with Leading Zeros
Indian PIN codes are 6 digits and never start with 0 (Maharashtra PINs start with 4), but exports still damage them – and product or employee codes often do start with zeros.
Before
| Store | Pincode (raw) | SKU (raw) |
|---|---|---|
| Kothrud | 411038.0 | 4512 |
| Gangapur Road | 422 013 | 87 |
| Sitabuldi | 440012 | 1203 |
| Hotgi Road | 41 3003 | 9 |
After
| Store | Pincode | SKU |
|---|---|---|
| Kothrud | 411038 | 004512 |
| Gangapur Road | 422013 | 000087 |
| Sitabuldi | 440012 | 001203 |
| Hotgi Road | 413003 | 000009 |
Steps in Excel
- Pincode as clean 6-character text:
=TEXT(VALUE(SUBSTITUTE(B2," ","")),"000000"). Check=LEN(C2)=6. - SKU with leading zeros restored as text:
=TEXT(C2,"000000"). - If the code only needs to look padded but stay a number: custom format
000000(1.6). - Import tip: in Power Query or the Text Import Wizard set these columns to Text so zeros are never lost.
Ravindra Bagale's Tip
CSV Excel madhe double-click karun ughadla ki khup students che codes "004512" che "4512" hotat, aani mag master sobat match hot nahit. Codes asnare columns import kartana Text mhanun set kara (Data › From Text/CSV kiwa Power Query). Aani lakshat theva – format ne zero disla tari value number ch aste; lookup donhi bajula same type havi.
Practice task
Restore 6-digit SKUs from numbers, clean pincodes with spaces and ".0", and verify that all pincodes in the Pune list start with 411 using =LEFT(C2,3)="411".