You bought a list, merged three trade-show exports, or inherited a "master_leads_FINAL_v3.csv". Before it goes anywhere near HubSpot, Salesforce, or Pipedrive, run this. No add-ons, no scripts, just five formulas.
1. Normalize the email into a helper column
In a new column next to your emails (say email is in column C):
=LOWER(TRIM(CLEAN(C2))) CLEAN drops invisible characters, TRIM drops stray spaces, and LOWER makes Jane@Acme.com and jane@acme.com the same key.
2. Flag duplicates with a running count
Next column:
=COUNTIF($D$2:D2, D2) The first time an email appears you get 1; every repeat gets 2, 3, and so on. Filter for anything greater than 1 to see exactly what you're about to remove.
3. Catch blank and broken emails
=IF(D2="","BLANK",IF(REGEXMATCH(D2,"^[^@\s]+@[^@\s]+\.[a-z]{2,}$"),"OK","BAD")) Rows marked BAD are usually typos like gmail,com or a phone number pasted in the wrong column. Fix or drop them now; a CRM import will reject them or, worse, accept them.
4. Split full names the CRM can map
If you only have "Full Name" in column B:
=SPLIT(TRIM(B2)," ",TRUE,TRUE) Then keep the first piece as First Name and join the rest as Last Name. Salesforce Leads require Last Name, so don't skip this.
5. Standardize company names for account matching
=PROPER(TRIM(REGEXREPLACE(E2,"(?i),?\s*(inc|llc|ltd|corp)\.?$",""))) Acme, Inc., ACME INC, and acme collapse to Acme, so contacts land on one account instead of three.
Then filter and export
Filter to running count = 1 and status = OK, copy to a new tab, and download as CSV (Sheets exports UTF-8 by default). That's your import file.
Want this as a one-page checklist? The HubSpot/Salesforce Import Checklist ($9) has every pre-import check in one place. If the list is already in your CRM and messy, the CRM Hygiene Scorecard ($29) grades your export and tells you what to fix first. More at pureservices.store.
