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 |
About this data: Teaching sample for a fictional shop “GreenField Dairy”. Figures and names (e.g. Priya Deshmukh) are made up for this lesson on phone numbers.
After
| Customer | Phone (10 digits) |
|---|---|
| Amir | 9000000011 |
| Zoya | 9000000012 |
| Raja | 9000000013 |
| Rani | 9000000014 |
| Salman | check source |
About this data: Demo table only — not real customer or company data. Built to practise phone numbers.
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".
Loading practice tasks...
- Remove spaces (Easy): Remove all spaces from B4.
- Remove dashes (Easy): Remove dashes from B7.
- Length of a clean number (Easy): How many digits are in C2?
- Last 4 digits (Easy): Get the last 4 digits of C2.
- Remove spaces and dash (Medium): Clean B2 of spaces and dashes.
- Last 10 digits (Medium): From the cleaned B2, keep only the last 10 digits.
- Is it 10 digits? (Medium): Is C7 exactly 10 digits long? (TRUE or FALSE)
- Masked number (Medium): Make masked text for C6: "XXXXXX" and then the last 4 digits.
- Valid mobile? (Hard): Is C4 valid? (TRUE or FALSE)
- Clean brackets too (Hard): Clean B3 of brackets and spaces.
- Count bad numbers (Hard): How many numbers in C2:C7 are NOT 10 digits?
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
