By DigiBoss Editorial Team · Last updated: October 16, 2026 · 9 min read

Why Cleaning Comes Before Campaigns

Raw lead files are rarely ready to use. They may contain extra spaces, mixed upper and lower case, numbers with +91 or dashes, blank rows and repeated people. If you import such a file straight into a CRM, you spread the mess into every report and automation you build on it.

Cleaning takes an hour but saves days of confusion later. It also protects your sender reputation, because invalid emails and numbers cause bounces and wasted calls. If you have not yet audited the file, run the checks in our database quality audit first.

Before You Start: Prepare Your Workspace

1. Save the original file in a folder named Original and never edit it.
2. Make a working copy and add the date to the name, for example leads_clean_2026-10-16.xlsx.
3. Add a new sheet called Log and note every change you make.
4. Check that the first row contains headings and that each column holds one type of data.
5. Know which columns your CRM requires, such as name, mobile, email, city and state.

If your file is huge, Excel may run slowly. Work on the columns you need and delete unused ones in the working copy.

Step 1: Trim Spaces and Remove Hidden Characters

Extra spaces break matching and duplicate checks. Add a helper column next to the name and use:

• =TRIM(CLEAN(A2)) removes extra spaces and non-printing characters.
• Copy the helper column and use Paste Special, Values only, over the original column.
• Delete the helper column when finished.

Do the same for city, state and email columns.

Step 2: Standardise Names and Locations

• Use =PROPER(A2) to turn RAHUL SHARMA or rahul sharma into Rahul Sharma.
• Use Find and Replace to fix common spelling variants of states and cities, for example Uttaranchal and Uttarakhand.
• Create a separate sheet with a master list of correct state names and use Data Validation or a lookup to flag mismatches.

Consistent names make segmentation reliable. A CRM that sees Delhi, New Delhi and Dilli as three cities will give you misleading reports.

Step 3: Fix Mobile Numbers

Mobile numbers cause the most trouble. Excel may show them in scientific notation, remove leading zeros or store them as numbers.

1. Format the mobile column as Text before you paste or edit values.
2. Remove spaces and dashes with =SUBSTITUTE(SUBSTITUTE(C2," ",""),"-","").
3. Keep the last ten digits with =RIGHT(SUBSTITUTE(SUBSTITUTE(C2," ",""),"-",""),10). This drops +91, 91 or 0 prefixes.
4. Test the result with =LEN(D2)=10 and filter for FALSE.
5. Check the first digit is 6, 7, 8 or 9 and remove clearly fake patterns such as 9999999999.

Paste the final result back as values and keep the column as text. Decide whether your CRM wants numbers with or without a country code and apply the same rule to every row.

Step 4: Check and Fix Emails

• Lowercase them with =LOWER(TRIM(D2)).
• Flag rows with no @ sign using =ISNUMBER(SEARCH("@",D2)).
• Look for typos in common domains and fix obvious ones, such as gmial.com.
• Remove role addresses such as info@ or admin@ if you are targeting individuals.

Syntax checks cannot tell whether an inbox exists. Use your email platform’s validation tools or a small pilot send to measure bounces before you send to the full list.

Step 5: Remove Blanks and Duplicates

1. Filter each key column for blanks and decide whether to delete the row or fill it. A row with no mobile and no email is useless for outreach.
2. Select the mobile column, then use Data, Remove Duplicates. Keep the row with more complete details by sorting first.
3. Record how many rows you removed in your Log sheet.

Also compare the cleaned file against your existing contacts. Use a lookup such as =COUNTIF(ExistingSheet!C:C,D2) to flag people you already have. Updating existing records is better than creating duplicates.

Step 6: Split and Combine Columns

• Use Data, Text to Columns to split a full name into first and last name if your CRM needs them.
• Use =A2&" "&B2 to combine columns when needed.
• Create a Segment column with values such as NEET-UP or Class12-Rajasthan that you can filter in your CRM.

Step 7: Segment and Add Tags

Segmentation turns one big file into many small campaigns. Add these helper columns before you import:

Mark consent status honestly. Third-party data is usually unknown consent, which limits the channels you should use. See the DPDP Act guide and TRAI telemarketing rules.

Step 8: Save as CSV UTF-8

1. Use File, Save As, then choose CSV UTF-8 (Comma delimited). UTF-8 keeps Hindi and other characters intact.
2. Reopen the CSV in a text editor to confirm that mobile numbers still show as ten digits.
3. Keep the .xlsx working copy as your master and use the CSV only for import.

If numbers show as 9.8E+09 inside the CSV, the column was not text. Go back, format it as text and re-enter the values.

Step 9: Import Into Your CRM

Every CRM has its own screen, but the pattern is the same across tools such as Zoho, HubSpot or LeadSquared.

1. Create custom fields for Source, Segment and Consent status if they do not exist.
2. Start the import and map each CSV column to the right CRM field.
3. Choose to update existing records based on mobile number or email, so you do not create duplicates.
4. Assign an owner or round-robin rule for telecallers.
5. Import a 50 to 100 row test file first and check how records look.
6. Import the full file, then check the total against your Log.

After import, build saved views for each segment so your team can start work quickly. Then plan outreach with our admission telecalling script and email marketing plan.

Common Cleaning Mistakes

• Editing the original file, with no way back.
• Letting Excel convert mobile numbers to numbers or scientific notation.
• Deleting duplicates without checking which row has more details.
• Importing without a Source tag, so results cannot be measured.
• Ignoring opt-outs from earlier campaigns.
• Sending to the full list before testing a pilot.

Excel Formula Cheat Sheet for Lead Lists

Keep this table next to your Log sheet. After you apply a formula to a whole column, always paste the results as values before you delete helper columns.

Handling Large Files and Excel Limits

Excel worksheets hold a little over one million rows, but files become slow long before that. If your list is very large, split it into smaller files by state or segment. This makes cleaning faster and also gives you ready-made campaign segments.

• Use filters to work on one state at a time.
• Turn off automatic calculation while you apply formulas to thousands of rows, then turn it back on.
• Save often and keep numbered versions of the file.
• For recurring cleaning jobs, learn Power Query in Excel. It records your cleaning steps so you can repeat them on every new file with one click.

If your CRM limits the size of an import, split the CSV into batches of the size it recommends and import them one by one.

Quality Gate Before You Import

Run this short gate on the final CSV. If any answer is no, fix it before importing.

1. Does every row have a valid ten-digit mobile or a valid email?
2. Are there zero duplicate mobiles in the file?
3. Are all state and city names spelled in a standard way?
4. Do Source, Segment and Consent status columns exist and have values?
5. Are opted-out and do-not-contact people removed?
6. Did you test a small import and review field mapping?

Passing the gate means your CRM will start clean. Failing it means you will pay for the clean-up later, with interest.

Before and After: A Worked Example

Here is how a few messy rows look before and after cleaning.

Small corrections like these prevent duplicate records and make reports accurate. They also help your callers, who can read names and numbers clearly.

Troubleshooting Common Import Errors

Keep a note of fixes in your Log sheet so the next import goes faster.

Automating Repeat Cleaning Jobs

If you clean a new lead file every month, automate the routine. Excel’s Power Query can record steps such as trimming spaces, changing case, removing duplicates and standardising columns. Next month, load the new file and refresh the query. You can also write a simple macro or use your CRM’s built-in import rules. Even a written checklist saves time and reduces mistakes.

Whatever method you choose, keep the original file, the cleaned file and the import log together in one folder named with the date and the vendor. That habit makes audits and troubleshooting much easier.

Frequently Asked Questions

How do I remove duplicate mobile numbers in Excel?

Select the mobile column in a copy of your file, then use Data, Remove Duplicates. For more control, add a helper column with COUNTIF and filter for values above 1.

How do I keep only ten digits of an Indian mobile number?

Use RIGHT with SUBSTITUTE to strip spaces and dashes and keep the last ten digits, for example =RIGHT(SUBSTITUTE(SUBSTITUTE(C2,” “,””),”-“,””),10).

Why do my mobile numbers show as 9.8E+09?

Excel treated the column as a number. Format the column as Text before entering or pasting values, then re-enter the numbers and save the CSV again.

Which file format should I import into a CRM?

Most CRMs accept CSV. Save as CSV UTF-8 so special characters are preserved, and keep your .xlsx file as the master copy.

Should I import the whole file at once?

No. Import a small test of 50 to 100 rows first, check the field mapping and duplicates, then import the rest.

How do I handle people who asked to stop contact?

Keep one suppression list with their details, add a Do not contact flag in your CRM and exclude them from every campaign and tool.

Sources and Further Reading

• Microsoft Support – official help for TRIM, SUBSTITUTE, RIGHT and Remove Duplicates
• MeitY (Government of India) – data protection law and rules
• TRAI (official site) – telemarketing rules