"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.

Want the full pre-import pass (dedupe, phones, emails, names, dates, picklists, addresses)? The HubSpot/Salesforce Import Checklist ($9) walks through it step by step. Or get a CRM Hygiene Scorecard ($29) on your actual list: send a sample and you get back a scored report of what will break before you import.