DEV Community: Sales List Rescue

Merging Mailchimp audiences? Dedupe your exports first (5 Google Sheets checks)

Sales List Rescue — Sun, 25 Oct 2026 20:22:21 +0000

Mailchimp recommends one primary audience, but plenty of accounts end up with three or four. The catch: Mailchimp dedupes only inside an audience. In its own words, "If the same person's email is in two audiences, we see that as two people" (Mailchimp Help).

The built-in Combine Audiences tool is quick, but it moves only subscribed contacts and doesn't transfer tags, groups, opt-in timestamps, signup sources or GDPR permissions. The safer route is to export every audience, stack the CSVs, clean them, and import back into your primary audience one status at a time. Here are the checks to run on the stacked file in Google Sheets before you import.

0. Before you stack: add two columns to every export

A Mailchimp export can come as a ZIP with separate CSVs for subscribed, unsubscribed, non-subscribed and cleaned contacts. Before stacking, add:

Audience: the old audience's name
Status: subscribed, unsubscribed, non-subscribed or cleaned (from the file name)

Stack your primary audience first, so its rows win when you dedupe.

1. Clean up email case and spaces, then find same-email duplicates

Case and stray spaces hide duplicates. In a helper column:

=LOWER(TRIM(A2))

Then count each cleaned email:

=COUNTIF($H$2:$H,H2)

Anything above 1 is the same person in two audiences.

2. Catch consent conflicts before they become a compliance problem

The dangerous duplicate is someone subscribed in one audience and unsubscribed in another. Flag emails that appear with more than one status:

=IF(COUNTIFS($H$2:$H,H2,$B$2:$B,"<>"&B2)>0,"CONFLICT","")

Resolve these by hand. Unsubscribed and cleaned addresses should go in as suppression, not as subscribers.

3. Don't lose tags when you merge rows

When two rows share an email but carry different TAGS or Audience values, decide which to keep before you delete the second row. A quick way to see them side by side:

=TEXTJOIN(" | ",TRUE,UNIQUE(FILTER($C$2:$C,$H$2:$H=H2)))

4. Flag invalid and blank emails

Mailchimp won't import broken addresses. A rough format check:

=IF(REGEXMATCH(H2,"^[^@\s]+@[^@\s]+\.[^@\s]+$"),"","CHECK")

This only checks the format. It doesn't verify that the mailbox exists.

5. Split by status before importing

Mailchimp imports one status at a time ("You can only import 1 status at a time"), and unmatched columns are dropped. Filter the cleaned sheet by Status, save each as its own UTF-8 CSV, and tick "Update any existing contacts" on import.

Two warnings from Mailchimp's docs

Combining is permanent: "This process is permanent and cannot be undone." Keep every export, including the primary audience's.
Pause welcome automations first, because the combine tool "will trigger the automation for all contacts moved from the original audience."

Rather not do it by hand? Stack your exports into one CSV and we'll clean it: same-email duplicates merged, possible duplicates and consent conflicts flagged, every change logged, your original file returned, back within 24h. Quick Clean, $25 for up to 200 rows. We never log in to your Mailchimp account. Full walkthrough of both merge routes: Mailchimp merge audiences guide. Or check your stacked file free in the browser first with the CSV Preflight Checker.

Which contacts are missing from your CRM? 5 Google Sheets formulas to compare two lead lists

Sales List Rescue — Sat, 24 Oct 2026 20:16:14 +0000

You have a fresh lead list and a CRM export, and you need to know which people are new, which are already in, and which changed. Eyeballing two sheets does not scale past a few dozen rows. These five formulas do it in Google Sheets with no add-ons. Put the new list on a tab called New and your CRM export on a tab called CRM, with email in column B on both.

1. Build a match key on both tabs

"Jane@Acme.com " and "jane@acme.com" are the same person, but a plain lookup says they are not. Add a key column (Z here) to each tab.

=LOWER(TRIM(B2))

Fill it down on both New and CRM, and match on Z from now on, never on the raw email.

2. Flag who is missing from the CRM

On the New tab, one column tells you whether each row already exists.

=IF(Z2="","NO EMAIL",IF(COUNTIF(CRM!$Z:$Z,Z2)=0,"MISSING","IN CRM"))

Run the same formula on the CRM tab pointed at New!$Z:$Z to see the reverse: contacts you have that the new list dropped.

3. Pull every missing row onto its own tab

Filtering by hand is where rows get lost. This spills the full missing rows into a new tab in one go.

=FILTER(New!A2:F,New!Z2:Z<>"",COUNTIF(CRM!Z2:Z,New!Z2:Z)=0)

Adjust A2:F to cover your columns. This tab is what you import, so nothing already in the CRM gets created twice.

4. Fall back to name plus company when email is blank

Rows with no email still need a key. Strip punctuation and common company suffixes so "Acme, Inc." and "acme inc" line up.

=LOWER(TRIM(A2))&"|"&TRIM(REGEXREPLACE(REGEXREPLACE(LOWER(C2),"[.,]",""),"\b(inc|llc|ltd|corp|co|gmbh)\b",""))

A is the full name and C is the company here. Use this key only for the NO EMAIL rows from step 2, and review those matches by eye, because two people can share a name.

5. Spot changed fields on rows that already exist

A contact can be in your CRM with an old phone number or title. Compare one field at a time; D is the phone column on both tabs in this example.

=IFERROR(IF(TRIM(XLOOKUP(Z2,CRM!$Z:$Z,CRM!$D:$D))=TRIM(D2),"SAME","CHANGED"),"NOT IN CRM")

Filter to CHANGED and decide row by row whether the new value should overwrite the old one. Do not bulk-overwrite; sometimes the CRM value is the one a rep fixed by hand.

Run all five on copies of both files, import only the step 3 tab as new contacts, and handle the CHANGED rows as updates.

Before you import the missing contacts, clean them. Drop your step 3 tab into the free CSV preflight checker to see what will break, right in your browser. Or send up to 200 rows to the $25 Quick Clean and get back a cleaned CSV (same-email duplicates merged, possible duplicates flagged) plus a change log within 24 hours, no tool login needed. Prefer to do it yourself? The HubSpot/Salesforce Import Checklist ($9) has every pre-import check on one page. Bigger lists and pricing at pureservices.store.

"Sr. VP Sales & Mktg" won't segment itself: 5 Google Sheets fixes for job titles before your CRM import

Sales List Rescue — Sat, 24 Oct 2026 15:19:52 +0000

Job titles are the column everyone wants to segment on and nobody cleans. One export gives you "VP Sales", "V.P., Sales", "Sr. Mgr - Marketing" and "n/a", and your "Directors and above" list quietly misses half of them. These five fixes run in Google Sheets before you import into HubSpot, Salesforce, Pipedrive, or your sequencer. Assume the raw title is in E and you work in F.

1. Clean the whitespace first

Exports carry double spaces, line breaks and non-breaking spaces that make identical titles look different.

=TRIM(REGEXREPLACE(CLEAN(E2),"[\s\x{00A0}]+"," "))

Put this in F and run every later step on F, not E.

2. Expand common abbreviations

"Sr.", "Mgr" and "Dir." should read the same as the long form so filters catch them.

=REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(F2,"(?i)\bsr\.?\s","Senior "),"(?i)\bmgr\b\.?","Manager"),"(?i)\bdir\b\.?","Director")

Add more pairs the same way (Eng to Engineering, Mktg to Marketing) once you see which ones your list uses.

3. Bucket seniority

One column you can filter on beats fifty spellings. VP is checked before C-level so "Vice President" doesn't land in the president bucket.

=IFS(REGEXMATCH(LOWER(G2),"\b(vp|v\.p|vice president|svp|evp|head of)\b"),"VP/Head",REGEXMATCH(LOWER(G2),"\b(ceo|cfo|cto|coo|cmo|cro|chief|founder|co-founder|owner|president)\b"),"C-level/Owner",REGEXMATCH(LOWER(G2),"\bdirector\b"),"Director",REGEXMATCH(LOWER(G2),"\b(manager|lead|supervisor)\b"),"Manager",TRUE,"IC/Other")

G is the output of step 2. Spot-check the "IC/Other" rows; that is where odd titles like "Partner" or "Principal" end up, and you decide where they belong.

4. Bucket department

Seniority without function still mixes your CFO with your CMO.

=IFS(REGEXMATCH(LOWER(G2),"sales|account exec|business development|\bsdr\b|\bbdr\b|revenue"),"Sales",REGEXMATCH(LOWER(G2),"marketing|growth|demand gen|brand|content"),"Marketing",REGEXMATCH(LOWER(G2),"engineer|developer|\bit\b|technology|software"),"Engineering/IT",REGEXMATCH(LOWER(G2),"operations|\bops\b|finance|\bhr\b|people"),"Ops/Finance/HR",TRUE,"Other")

Keep the raw title too. The buckets are for filtering, and the original is what you show in a personalized line.

5. Flag junk titles for review

Blank cells, placeholders, pasted emails or URLs, and whole bios stuffed into the title field should never reach a merge field.

=IF(OR(LEN(TRIM(E2))=0,REGEXMATCH(LOWER(TRIM(E2)),"@|http|^(n/?a|none|test|-+|\.+)$"),LEN(E2)>80),"REVIEW","OK")

Filter to REVIEW, fix or blank those rows, and leave title out of your opener for anything you blank.

Run all five on a copy, sort by the seniority column to eyeball each bucket, then import only the cleaned sheet with the two bucket columns mapped to custom properties.

Rather hand off the cleanup? Drop your file into the free CSV preflight checker to see what will break, right in your browser. Or send up to 200 rows to the $25 Quick Clean and get back a cleaned CSV (same-email duplicates merged, possible duplicates flagged) plus a change log within 24 hours, no tool login needed. Prefer to do it yourself? The HubSpot/Salesforce Import Checklist ($9) has every pre-import check on one page. Bigger lists and pricing at pureservices.store.

Hi {{first_name}}, ACME INC. LLC: 5 Google Sheets fixes for merge fields before your cold email tool sends them

Sales List Rescue — Sat, 24 Oct 2026 14:26:41 +0000

A list can pass every bounce check and still embarrass you on the first send. Shouting caps, "Hi ," with a blank name, and "ACME HOLDINGS, INC." in the middle of a sentence all come from the merge fields, not the addresses. These five fixes run in Google Sheets before you upload to Instantly, Smartlead, lemlist, or any sender that uses variables. Assume first name is in B, company in D, email in C.

1. Fix first-name casing

Exports mix "JOHN", "john" and "John". Normalize to proper case:

=PROPER(TRIM(B2))

Watch for names like "McDonald" or "O'Neil"; PROPER handles the apostrophe but not "Mc", so scan anything starting with "Mc" or "Mac" by hand.

2. Give blank or junk names a safe fallback

A missing name sends "Hi ,". So does a placeholder like "-", "n/a" or "test". This is a Google Sheets formula (REGEXMATCH is Sheets-only):

=IF(OR(LEN(TRIM(SUBSTITUTE(B2,CHAR(160)," ")))<2,REGEXMATCH(LOWER(TRIM(SUBSTITUTE(B2,CHAR(160)," "))),"^(n/?a|none|test|unknown|-+)$")),"there",PROPER(TRIM(SUBSTITUTE(B2,CHAR(160)," "))))

Why the SUBSTITUTE: CRM and LinkedIn exports can contain invisible non-breaking spaces (CHAR(160)) that TRIM does not remove, so a name that looks blank can still print "Hi ,".
Now every row reads "Hi John" or "Hi there", never "Hi ,".

3. Strip legal suffixes from company names

Nobody says "I saw ACME HOLDINGS, INC. is hiring." Drop the suffix and fix the caps:

=PROPER(TRIM(REGEXREPLACE(D2,"(?i)[,.]?\s*\b(inc|llc|ltd|limited|corp|corporation|co|gmbh|plc|pty)\.?$","")))

Then eyeball acronyms like "IBM" or "NASA" that PROPER turns into "Ibm"; keep a short exceptions list and fix those rows by hand.

4. Catch a first name that is actually a full name

Some forms put "John Smith" in the first-name field, so your opener reads "Hi John Smith".

=IF(REGEXMATCH(TRIM(B2),"\s"),REGEXEXTRACT(TRIM(B2),"^\S+"),TRIM(B2))

Run it before step 1 and step 2 so the fallback and casing apply to the cleaned value.

5. Fill a missing company from the email domain

If company is blank but the email is a work address, the domain is a decent stand-in:

=IF(LEN(TRIM(D2))>0,D2,IF(REGEXMATCH(LOWER(C2),"@(gmail|yahoo|hotmail|outlook|icloud|aol)\."),"",PROPER(REGEXEXTRACT(LOWER(C2),"@([^.]+)\."))))

Free-mail addresses stay blank, so you can route them to a sequence that doesn't mention a company at all.

Run all five on a copy, preview three or four rows in your tool's variable preview, then upload only the cleaned sheet.

Rather not fix it by hand? Drop your file into the free CSV preflight checker to see what will break, right in your browser. Or send up to 200 rows to the $25 Quick Clean and get back a cleaned CSV (same-email duplicates merged, possible duplicates flagged) plus a change log within 24 hours, no tool login needed. Prefer to do it yourself? The HubSpot/Salesforce Import Checklist ($9) has every pre-import check on one page. Bigger lists and pricing at pureservices.store.

Duplicate contacts sneaking into your CRM import? 5 Google Sheets checks to catch them first

Sales List Rescue — Sat, 24 Oct 2026 10:18:37 +0000

HubSpot dedupes on email, Salesforce on whatever matching rule you set, and Pipedrive barely at all. None of them catch "Jon Smith, jon@acme.com" and "Jonathan Smith, JON@Acme.com " as the same person. Every dupe that slips through means split activity history, double sequences, and reps stepping on each other. These five Google Sheets checks find duplicates before you upload. Assume email is in column C, first name in A, last name in B, company in D, and phone in E.

1. Build a clean email key

=LOWER(TRIM(C2))

Put this in a helper column (say F). Case and stray spaces are the most common reason two identical emails look different to a sheet but identical to your CRM's dedupe, or the other way round.

2. Flag exact email duplicates

=IF(F2="","NO EMAIL",IF(COUNTIF(F$2:F,F2)>1,"DUPE","OK"))

Filter for DUPE and keep the row with the most filled-in fields. =COUNTA(A2:E2) in another column makes picking the winner fast.

3. Catch Gmail dot and plus variants

j.smith+leads@gmail.com and jsmith@gmail.com are the same inbox. In column G:

=IF(REGEXMATCH(F2,"@(gmail|googlemail)\.com$"),SUBSTITUTE(REGEXEXTRACT(F2,"^[^@+]*"),".","")&"@gmail.com",REGEXREPLACE(F2,"\+[^@]*",""))

Gmail addresses lose dots and plus tags; everything else just loses the plus tag. Re-run check 2 against column G.

4. Match people without an email on name + company

=LOWER(TRIM(A2))&"|"&LOWER(TRIM(B2))&"|"&LOWER(REGEXREPLACE(TRIM(D2),"(?i)[,.]?\s*(inc|llc|ltd|limited|corp|co|gmbh)\.?$",""))

Then =COUNTIF(H$2:H,H2)>1 flags rows where "Acme Inc." and "acme" are the same company. Review these by hand; two real people can share a name.

5. Match on phone digits as a tiebreaker

=RIGHT(REGEXREPLACE(E2,"\D",""),10)

Last 10 digits strips country codes and formatting, so "+1 (555) 010-2233" and "555.010.2233" line up. Flag with COUNTIF the same way.

Run all five on a copy, merge or delete the flagged rows, then import only the cleaned sheet. A quick final count, =COUNTIF(I2:I,"DUPE") on your final flag column, should read 0.

Rather hand off the cleanup? Send up to 200 rows to the $25 Quick Clean and get back a cleaned CSV (same-email duplicates merged, possible duplicates flagged) plus a change log within 24 hours. No CRM login needed: email us your CSV (reply to your receipt or send it to slrescue@mail.grokbot.com). Bigger list? Intro $99 covers up to 500 rows, and Growth ($299, up to 2,000) and Bulk ($499, up to 5,000) are on pureservices.store. Want to check the file first? The free CSV preflight checker runs in your browser.

Country and state columns breaking your CRM import? 5 Google Sheets fixes for addresses

Sales List Rescue — Sat, 24 Oct 2026 08:20:34 +0000

"USA", "United States", "U.S.", and "us" are four different countries as far as your CRM is concerned. State columns are worse: "Calif.", "CA", "california ", and a stray ZIP code in the state field. HubSpot and Salesforce both validate country/state picklists, so one messy column either rejects rows or quietly breaks territory routing. These five Google Sheets fixes clean it up before you upload. Assume country is in column D, state in E, and ZIP/postal code in F.

1. Normalize spacing and case first

=PROPER(TRIM(REGEXREPLACE(D2,"[.\s]+"," ")))

This alone collapses most variants ("u.s." becomes "U S", "united states" becomes "United States"), so the next step has far fewer cases.

2. Map countries to one standard with a lookup tab

Make a tab called country_map with lowercase variants in column A and the CRM-accepted name (or ISO code) in column B:

=IFERROR(VLOOKUP(LOWER(TRIM(D2)),country_map!A:B,2,FALSE),"UNMAPPED")

Seed it with =SORT(UNIQUE(LOWER(TRIM(D2:D)))) so you only map values you actually have.

3. Convert full state names to two-letter codes

Put full names in states!A and codes in states!B, then:

=IF(LEN(TRIM(E2))=2,UPPER(TRIM(E2)),IFERROR(VLOOKUP(LOWER(TRIM(E2)),states!A:B,2,FALSE),"CHECK"))

Already-correct codes pass through; everything else gets mapped or flagged.

4. Rescue ZIP codes that lost their leading zero

Sheets turns 02134 into 2134. For US rows:

=IF(AND(G2="United States",ISNUMBER(--F2)),TEXT(F2,"00000"),F2)

Format the column as Plain text before you export so the zeros survive the CSV.

5. Count what's left before you import

=COUNTIF(G2:G,"UNMAPPED")+COUNTIF(H2:H,"CHECK")

Import when this reads 0, using the cleaned columns instead of the originals.

Run these on a copy, fix the flagged rows, then map the cleaned columns in your importer.

Picklist errors killing your CRM import? 5 Google Sheets fixes for lead source, stage, and owner columns

Sales List Rescue — Sat, 24 Oct 2026 07:26:09 +0000

"Invalid value for Lead Source" is the import error that eats an afternoon. Your CSV says "linkedin", "LinkedIn Ads", and "LI" while your CRM only accepts "LinkedIn". Same story for deal stage, industry, country, and record owner. These five Google Sheets fixes map every messy value to an allowed one before you upload. Assume the messy value is in column F.

1. See every distinct value you actually have

=SORT(UNIQUE(TRIM(F2:F)))

This short list is your cleanup to-do, usually 20 to 40 variants instead of thousands of rows.

2. Build a mapping tab

Make a tab called map with the messy value in column A and the CRM-allowed value in column B. Then:

=IFERROR(VLOOKUP(LOWER(TRIM(F2)),map!A:B,2,FALSE),"UNMAPPED")

Put lowercase values in map!A so "LinkedIn " and "linkedin" hit the same row.

3. Flag values the CRM will reject

Paste the CRM's allowed picklist into allowed!A:A:

=IF(COUNTIF(allowed!A:A,G2)=0,"REJECT","")

4. Map owners by email, not name

Importers match owners on user email. Turn "Sam K." into the login:

=IFERROR(VLOOKUP(TRIM(H2),owners!A:B,2,FALSE),"default-owner@yourco.com")

Pick a real default owner so nothing lands unassigned.

5. Count what's left before you import

=COUNTIF(G2:G,"UNMAPPED")+COUNTIF(I2:I,"REJECT")

Import when this reads 0.

Run these on a copy, fix the UNMAPPED and REJECT rows, then import only the mapped columns.

Dates breaking your CRM import? 5 Google Sheets fixes for close dates, birthdays, and last-contacted fields

Sales List Rescue — Sat, 24 Oct 2026 06:14:55 +0000

A CSV that says 03/04/2026 means March 4 to HubSpot and 3 April to a European Salesforce org. Mixed date formats are the quiet reason imports "succeed" but your close dates, renewal dates, and last-contacted fields come out wrong or blank. These five Google Sheets fixes get every date into one safe format before you upload. Assume the raw date is in column D.

1. Find the dates that are really text

Sheets left-aligns text and right-aligns real dates. Flag the text ones:

=IF(ISNUMBER(D2),"date","TEXT - fix me")

2. Convert text dates to real dates

=IFERROR(DATEVALUE(TRIM(D2)),"")

Blanks in the result are the rows a human needs to look at.

3. Fix day/month swaps from European exports

If the source was DD/MM/YYYY, rebuild it explicitly instead of trusting your locale:

=DATE(RIGHT(D2,4),MID(D2,4,2),LEFT(D2,2))

4. Output ISO format for the importer

Most CRMs accept YYYY-MM-DD without guessing:

=TEXT(E2,"yyyy-mm-dd")

5. Catch impossible values

Close dates in 1900, birthdays in the future, last-contacted dates after today:

=IF(OR(E2<DATE(2000,1,1),E2>TODAY()+730),"CHECK","")

Run all five on a copy, filter for "TEXT", blanks, and "CHECK", fix those by hand, then import only the ISO column.

Messy names and company fields? 6 Google Sheets fixes before your next CRM import

Sales List Rescue — Sat, 24 Oct 2026 05:21:09 +0000

"Hi JOHN," and "Acme Inc" next to "ACME, Inc." are the two fastest ways to look sloppy in outreach and split one account into three in your CRM. These six formulas clean name and company columns in Google Sheets before you import. Assume full name is in A and company is in C.

1. Fix capitalization

PROPER handles ALL CAPS and all-lowercase names in one pass.

=PROPER(TRIM(A2))

Spot-check names like McDonald, O'Neil, and van der Berg afterwards; no formula gets every one right.

2. Split full name into first and last

First: =IFERROR(LEFT(B2,FIND(" ",B2)-1),B2)
Last:  =IFERROR(REGEXEXTRACT(B2,"\s(\S+)$"),"")

Middle names drop out of both columns, which is usually what a CRM import wants.

3. Strip titles and suffixes

Dr., Mr., Ms., Jr., III, and PhD end up in the first or last name field and break personalization.

=TRIM(REGEXREPLACE(B2,"(?i)^(dr|mr|mrs|ms|prof)\.?\s+|,?\s+(jr|sr|ii|iii|phd|mba)\.?$",""))

4. Flag placeholder names

Scraped and form-fill lists are full of "Test", "N/A", "asdf", and email addresses typed into the name box.

=IF(REGEXMATCH(LOWER(B2),"^(test|n/?a|none|asdf|unknown|-)$|@"),"placeholder","")

5. Normalize company suffixes

So "Acme Inc", "ACME, Inc.", and "Acme Incorporated" match as one account.

=TRIM(REGEXREPLACE(PROPER(C2),"(?i),?\s+(inc|incorporated|llc|ltd|limited|corp|corporation|co|gmbh)\.?$",""))

Keep the original column too, and import the cleaned one as a matching key rather than the display name if your legal names matter.

6. Find company duplicates

=IF(COUNTIF(D$2:D2,D2)>1,"dup company","")

Duplicate companies aren't always wrong (several contacts per account is normal), but they tell you where your CRM will create duplicate company records unless you match on domain.

Before you import

Map first name, last name, and the cleaned company to separate CRM fields, and match companies on website domain where your CRM allows it. Name matching alone will still miss "IBM" versus "International Business Machines".

Cold email list bouncing? 6 Google Sheets checks before you import it into your CRM or sender

Sales List Rescue — Sat, 24 Oct 2026 03:25:35 +0000

High bounce rates burn your sending domain fast, and most bad addresses are visible in the spreadsheet before you ever upload. Run these six checks in Google Sheets first. Assume the email column is A.

1. Trim and lowercase

Hidden spaces and mixed case create fake duplicates and failed matches.

=LOWER(TRIM(A2))

2. Flag broken syntax

Catches missing @, spaces inside the address, and missing domain endings.

=IF(REGEXMATCH(B2,"^[a-z0-9._%+\-]+@[a-z0-9.\-]+\.[a-z]{2,}$"),"ok","bad syntax")

3. Catch common domain typos

gmial.com, gmail.co, hotmial.com, and yahooo.com show up in every scraped list.

=IF(REGEXMATCH(B2,"@(gmial|gmai|gnail|hotmial|yahooo)\.|@gmail\.co$"),"typo","")

Fix the obvious ones by hand; delete anything you can't be sure of.

4. Separate role addresses

info@, sales@, support@, and admin@ rarely reach a decision-maker and often trigger spam complaints.

=IF(REGEXMATCH(B2,"^(info|sales|support|admin|contact|hello|office|team|noreply|no-reply)@"),"role","")

5. Remove duplicates

=IF(COUNTIF(B$2:B2,B2)>1,"duplicate","")

Keep the first row, filter on "duplicate", and delete the rest.

6. Keep personal and work addresses apart

For B2B outreach, tag free-mail domains so you can decide whether to keep them:

=IF(REGEXMATCH(B2,"@(gmail|yahoo|hotmail|outlook|aol|icloud)\."),"free mail","work")

Then verify a sample

Syntax checks can't tell you whether a mailbox exists. Run a small sample through your verifier of choice, and if more than a few percent fail, verify the whole list before sending.

Phone numbers breaking your CRM import or dialer? 5 Google Sheets fixes for E.164 format

Sales List Rescue — Sat, 24 Oct 2026 02:17:54 +0000

Phone columns are the quietest way to wreck a lead list. HubSpot, Salesforce, Pipedrive, and most dialers want one consistent format, usually E.164 (+14155550123). Exports give you (415) 555-0123, 415.555.0123 ext 4, and 00 44 20 7946 0958 in the same column. Fix it in Sheets before you import.

1. Strip everything that isn't a digit or a leading plus

=REGEXREPLACE(TRIM(A2),"[^\d+]","")

2. Split off extensions first

Extensions get glued onto the number once punctuation is removed. Pull them into their own column before step 1:

=IFERROR(REGEXEXTRACT(LOWER(A2),"(?:ext\.?|x)\s*(\d+)$"),"")

and remove them from the main value:

=TRIM(REGEXREPLACE(LOWER(A2),"(?:ext\.?|x)\s*\d+$",""))

3. Convert 00 international prefixes to +

=IF(LEFT(B2,2)="00","+"&MID(B2,3,20),B2)

4. Add a country code where it's missing

For a US/Canada list, a bare 10-digit number gets +1; an 11-digit number starting with 1 gets +:

=IF(LEFT(C2,1)="+",C2,IF(LEN(C2)=10,"+1"&C2,IF(AND(LEN(C2)=11,LEFT(C2,1)="1"),"+"&C2,C2)))

Use your own country's code if the list is mostly local numbers, and use the Country column when you have one.

5. Flag anything that still looks wrong

=IF(REGEXMATCH(D2,"^\+\d{8,15}$"),"ok","check")

Filter on "check" and fix or blank those rows by hand. A blank phone field is better than a wrong one that your reps dial.

Test ten rows first

Import ten rows, open the records, and click-to-call one. If it dials correctly, import the rest.

Exported leads from Apollo or a scraper? 6 cleanup steps before they touch your CRM

Sales List Rescue — Sat, 24 Oct 2026 01:24:06 +0000

Lead exports from Apollo, ZoomInfo, LinkedIn tools, or a scraper look tidy in the preview, then make a mess the moment they hit HubSpot, Salesforce, or Pipedrive. Spend ten minutes on these six steps in Google Sheets first.

1. Drop the columns you will never use

Exports often ship 40+ columns. Keep only what maps to a real CRM property: first name, last name, email, company, title, phone, website, country, and source. Every extra column is another mapping you can get wrong.

2. Normalize emails and remove repeats

=LOWER(TRIM(C2))

Then flag duplicates with =COUNTIF($D$2:D2,D2) and keep rows where it equals 1. Run the same check against an export of your current CRM contacts so you don't re-import people you already have:

=IF(COUNTIF(CRM!A:A,D2)>0,"already in CRM","new")

3. Filter out risky emails

Remove rows marked "guessed", "unverified", or "catch-all" if your tool provides a status column. Also drop role inboxes you don't want in sequences:

=REGEXMATCH(D2,"^(info|sales|support|admin|hello|contact)@")

Bounces from a bad batch hurt your sending domain far more than a smaller list does.

4. Fix name casing

Exports mix JOHN, john, and John. One formula fixes the first-name field in emails and greetings:

=PROPER(TRIM(A2))

Spot-check names like McDonald or O'Neil afterwards.

5. Make company names match your accounts

Acme Inc., ACME, and Acme, Inc become three companies in your CRM. Strip common suffixes before import so association rules can match:

=TRIM(REGEXREPLACE(PROPER(E2),"(?i),?\s*(inc|llc|ltd|corp|co)\.?$",""))

Better still, match on website domain when your CRM supports it.

6. Tag the source and import date

Add a Lead Source column (for example "Apollo export") and an import date. When a batch turns out to be bad, you can find and remove exactly those records in one filter instead of guessing.

Test ten rows first

Import ten rows, open a few records, and confirm every mapped field filled and no duplicate was created. Then import the rest.