How to Clean a Phone Number List in Excel Before an Admission Campaign (Step-by-Step)
By the Digiboss Team | Last updated: 17 October 2026 | Questions? Write to help@digiboss.in
A clean list is the cheapest improvement you can make to any admission campaign. This tutorial shows how to clean a phone number list in Excel step by step. It works for any student list in Excel or CSV, including ones from your own enquiries and purchased files such as the Digiboss Class 10 database.
You do not need advanced Excel skills. Google Sheets uses the same formulas.
Before you start
- Save a backup copy of the original file. Never edit your only copy.
- Work in a new sheet called “Clean”.
- Add a column named “Status” where you will note results.
Step 1: Format the phone column as text
Select the phone column, right-click, choose Format Cells and set it to Text. This prevents Excel from converting long numbers into scientific notation (for example 9.8E+09) and from dropping leading zeros.
Step 2: Trim spaces and hidden characters
In a helper column, use:
=TRIM(CLEAN(C2))
Fill down. This removes extra spaces and non-printing characters. Copy the helper column and Paste Special as Values over the original.
Step 3: Keep only the last 10 digits
Many lists contain +91, 91 or a leading zero. Use:
=RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2," ",""),"-",""),"+",""),10)
This removes spaces, hyphens and plus signs, then keeps the final 10 characters.
Step 4: Validate Indian mobile numbers
Indian mobile numbers have 10 digits and start with 6, 7, 8 or 9. In a new column:
=IF(AND(LEN(E2)=10,ISNUMBER(--E2),OR(LEFT(E2,1)="6",LEFT(E2,1)="7",LEFT(E2,1)="8",LEFT(E2,1)="9")),"OK","Check")
Filter the column for “Check” and either fix or remove those rows. Remember that this only checks format. It cannot tell you whether the number is active.
Step 5: Remove duplicates
Select the cleaned phone column, go to the Data tab and choose Remove Duplicates. Tick only the phone column if you want one record per number. Note how many rows were removed; it is a quick quality signal.
Step 6: Standardise names and states
- Use
=PROPER(TRIM(B2))to fix name capitalisation. - Sort the State column A to Z and look for spelling variants (for example “UP” and “Uttar Pradesh”). Use Find and Replace to standardise.
Step 7: Split the list by state
- Apply a Filter to the header row.
- Filter by one state.
- Copy visible rows into a new sheet named after the state.
- Repeat for each state you plan to target.
This sets you up for state-wise campaigns.
Step 8: Add tracking columns
Add these columns before you start:
| Column | Purpose |
|---|---|
| Channel | WhatsApp, SMS or call |
| Date contacted | Follow-up timing |
| Response | Replied, interested, not interested |
| Opt-out | Yes or No |
| Notes | Counsellor remarks |
Mark opt-outs and exclude them from every future campaign.
Step 9: Export as CSV (UTF-8)
Go to File, Save As, and choose CSV UTF-8. This keeps regional language characters intact when you import into a CRM, WhatsApp Business tool or bulk SMS platform.
Quick quality checklist
- Backup saved
- Phone column formatted as text
- 10-digit numbers only, starting with 6 to 9
- Duplicates removed
- States standardised
- Opt-out column created
- CSV exported in UTF-8
Before you send anything
A clean list is not the same as a permitted list. Check the data source, use DLT-approved templates for commercial SMS, ensure WhatsApp opt-in expectations are met and read the DPDP Act guide, because many Class 10 students are under 18.
Key takeaways
- Format, trim, validate, de-duplicate and segment in that order.
- Track results and opt-outs in the same sheet.
- Export as UTF-8 CSV for smooth imports.
Frequently Asked Questions
How do I stop Excel from changing phone numbers into scientific notation?
Format the phone column as Text before pasting or typing numbers, or import the CSV using the Text option for that column.
How do I validate Indian mobile numbers in Excel?
Check that the number has 10 digits and starts with 6, 7, 8 or 9 using a formula with LEN, LEFT and ISNUMBER. This validates format only, not whether the number is active.
What is the best format to import a list into a CRM?
A clean CSV saved as UTF-8 with one record per row and clear column headings imports reliably into most CRM and messaging tools.