5.10 Phone Numbers
All numbers below are dummy numbers for practice.
Before
| Customer | Phone (raw) |
|---|---|
| Amir | +91 90000 00011 |
| Zoya | 090000-00012 |
| Raja | 91 9000000013 |
| Rani | (+91) 90000 000 14 |
| Salman | 9.00000E+09 |
After
| Customer | Phone (10 digits) |
|---|---|
| Amir | 9000000011 |
| Zoya | 9000000012 |
| Raja | 9000000013 |
| Rani | 9000000014 |
| Salman | check source |
Steps in Excel
-
Remove spaces, hyphens, brackets and plus signs, then keep the last 10 digits:
=RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," ",""),"-",""), "+",""), "(",""), ")",""),10) -
Validate:
=AND(LEN(C2)=10, ISNUMBER(--C2), OR(LEFT(C2)="6",LEFT(C2)="7",LEFT(C2)="8",LEFT(C2)="9"))– Indian mobile numbers are 10 digits starting 6–9. - Want "+91 90000 00011" for display?
="+91 "&LEFT(C2,5)&" "&RIGHT(C2,5). - Store phone numbers as Text (format the column as Text before typing or importing) so Excel never shows them in scientific notation like 9.00000E+09. If that already happened and digits were lost, go back to the source.
Ravindra Bagale's Tip
Many students give phone numbers the Number format – then Excel shows them as 9.00E+09, or the last digits are lost when you save as CSV. Phone numbers, pincodes and Aadhaar-like "numbers" are not for maths; they are codes – keep them as Text. After cleaning, always add a LEN = 10 check.
Ravindra Bagale's Tip – मराठी
Phone number ला बरेच students Number format देतात – मग Excel ते 9.00E+09 असं दाखवतो किंवा CSV save केल्यावर शेवटचे digits जातात. Phone, pincode, Aadhaar सारखे "numbers" गणितासाठी नाहीत, ते codes आहेत – Text म्हणून ठेवा. Clean केल्यावर LEN = 10 चा check नक्की लावा.
Ravindra Bagale's Tip – हिंदी
Phone number को बहुत से students Number format दे देते हैं – फिर Excel उसे 9.00E+09 दिखाता है या CSV save करने पर आख़िरी digits चले जाते हैं. Phone, pincode, Aadhaar जैसे "numbers" गणित के लिए नहीं हैं, ये codes हैं – इन्हें Text रखो. Clean करने के बाद LEN = 10 का check ज़रूर लगाओ.
Practice task
Clean 12 dummy phone numbers in mixed formats into 10 digits, flag invalid ones with the validation formula, and create a display column "+91 XXXXX XXXXX".