Ravindra BagaleCourses & study guides

5. Data Cleaning A–Z

5.10 Phone Numbers

By Last updated:

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

  1. Remove spaces, hyphens, brackets and plus signs, then keep the last 10 digits:

    =RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," ",""),"-",""),"+",""),"(",""),")",""),10)

  2. 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.

  3. Want "+91 90000 00011" for display? ="+91 "&LEFT(C2,5)&" "&RIGHT(C2,5).
  4. 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

Phone number la khup students Number format detat – mag Excel te 9.00E+09 asa dakhavto kiwa CSV save kelyavar shevtche digits jaatat. Phone, pincode, Aadhaar-sarkhe "numbers" he maths sathi nahit, te codes aahet – Text mhanun theva. Clean kelyavar LEN = 10 cha check nakki lava.

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".