"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.
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.
Top comments (0)