5.12 Pincodes and Codes with Leading Zeros
Codes with leading zeros using TEXT with example — in 30 seconds
What: TEXT with a format like "000000" adds the missing zeros in front of a code and returns it as text.
Why: Excel drops leading zeros from numbers. Product code 000451 becomes 451, and lookups against the master fail.
Tiny example: =TEXT(C2,"000000") turns 451 into 000451 and 12 into 000012.
Codes with leading zeros using TEXT with example — 30 सेकंदात
काय: "000000" सारख्या format सह TEXT code च्या पुढे गेलेले zeros परत लावतो आणि तो text म्हणून देतो.
का: Excel numbers मधले पुढचे zeros काढून टाकतो. Product code 000451 चा 451 होतो, आणि master शी lookup fail होतो.
छोटं उदाहरण: =TEXT(C2,"000000") 451 चं 000451 आणि 12 चं 000012 करतो.
Codes with leading zeros using TEXT with example — 30 सेकंड में
क्या: "000000" जैसे format के साथ TEXT code के आगे के छूटे zeros वापस लगाता है और उसे text के रूप में देता है.
क्यों: Excel numbers के आगे वाले zeros हटा देता है. Product code 000451 बन जाता है 451, और master से lookup fail होता है.
छोटा उदाहरण: =TEXT(C2,"000000") 451 को 000451 और 12 को 000012 बनाता है.
First look at the data (before → after):
Expected result — Six-character codes
| C: Code | D: Fixed code | LEN |
|---|---|---|
| 451 | 000451 | 6 |
| 12 | 000012 | 6 |
| 4100 | 004100 | 6 |
About this data: Teaching sample for a fictional shop “Skyline Clinics”. Figures and names (e.g. Neha Shah) are made up for this lesson on pincodes and codes with leading zeros.
Before table and formula as text
Before — Product codes after import (zeros lost)
| C: Code |
|---|
| 451 |
| 12 |
| 4100 |
About this data: Demo table only — not real customer or company data. Built to practise pincodes and codes with leading zeros.
Formula / click path
D2: =TEXT(C2,"000000")
check: =LEN(D2)=6
Do it step by step:
- Click D2 and type
=TEXT(C2,"000000"). - Copy down.
- Check the length with
=LEN(D2)=6. Every row should say TRUE. - Copy column D and Paste Special › Values over column C.
- Next time, set the column to Text in Power Query or the import wizard before loading.
स्टेप बाय स्टेप करून बघा:
- D2 वर click करा आणि
=TEXT(C2,"000000")type करा. - खाली copy करा.
=LEN(D2)=6ने length check करा. प्रत्येक row ला TRUE यायला हवं.- Column D copy करा आणि column C वर Paste Special › Values करा.
- पुढच्या वेळी load करण्याआधी Power Query किंवा import wizard मध्ये column Text करा.
स्टेप बाय स्टेप करके देखें:
- D2 पर click करें और
=TEXT(C2,"000000")type करें. - नीचे copy करें.
=LEN(D2)=6से length check करें. हर row में TRUE आना चाहिए.- Column D copy करें और column C पर Paste Special › Values करें.
- अगली बार load करने से पहले Power Query या import wizard में column को Text करें.
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 |
About this data: Made-up rows so you can copy the pattern for pincodes and codes with leading zeros. Currencies may use ₹, £, € or $.
After
| Store | Pincode | SKU |
|---|---|---|
| Kothrud | 411038 | 004512 |
| Gangapur Road | 422013 | 000087 |
| Sitabuldi | 440012 | 001203 |
| Hotgi Road | 413003 | 000009 |
About this data: Fictional worksheet used in class. Safe to edit; nothing here is live production data.
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
When a CSV is opened in Excel by double-clicking, many students' codes turn from "004512" into "4512", and then they don't match the master. When importing columns that contain codes, set them as Text (Data › From Text/CSV or Power Query). And remember – even if a format shows the zero, the value is still a number; a lookup needs the same type on both sides.
Ravindra Bagale's Tip – मराठी
CSV Excel मध्ये double-click करून उघडली की बऱ्याच students चे codes "004512" चे "4512" होतात, आणि मग master सोबत match होत नाहीत. Codes असलेले columns import करताना Text म्हणून set करा (Data › From Text/CSV किंवा Power Query). आणि लक्षात ठेवा – format ने zero दिसला तरी value number च असते; lookup साठी दोन्ही बाजूला same type हवा.
Ravindra Bagale's Tip – हिंदी
CSV को Excel में double-click करके खोलते ही बहुत से students के codes "004512" से "4512" हो जाते हैं, और फिर master से match नहीं होते. Codes वाले columns import करते समय उन्हें Text set करो (Data › From Text/CSV या Power Query). और याद रखो – format से zero दिख भी जाए तो value number ही रहती है; lookup के लिए दोनों तरफ़ same type चाहिए.
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".
Loading practice tasks...
- Fix a US ZIP (Easy): Show C3 as 5 digits.
- Fix a short ZIP (Easy): Show C5 as 5 digits.
- Item code with zeros (Easy): Show E2 as 6 digits.
- Length of a number (Easy): How many digits does C3 show?
- Is it a number or text? (Medium): Is D3 text? (TRUE or FALSE)
- Do they match? (Medium): Show C5 as a 5-digit code with leading zeros. Is it the same as D5? (TRUE or FALSE)
- Item code with prefix (Medium): Make "SKU-000123" from E4.
- Zeros with RIGHT (Medium): Show E3 as 6 digits using RIGHT.
- Right length by country (Hard): Show C4 with the right length for its country (B4).
- Same for a US row (Hard): Show C3 with the right length for its country.
- Count lost zeros (Hard): How many numbers in C2:C7 are shorter than 5 digits?
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
