Preserve the original address, create separate cleaning columns, remove non-address notes, normalize whitespace, keep postal codes as text, and add city, region and country context. Test a representative sample before applying transformations to the full dataset.
Keep raw data separate from cleaned data
Never overwrite the only copy of an imported customer or property address. Add cleaned helper columns and preserve the source for audit, correction and comparison. A stable record ID should travel with both versions.
| Structure | Advantages | Watch for |
|---|---|---|
| One combined column | Easy selection and export | Harder to detect a missing city or postal code |
| Separated components | Simple validation and filtering | Fields must remain adjacent and in the correct order |
| Raw plus cleaned | Best traceability and safe experiments | Requires additional columns |
Use formulas for reversible cleanup
TRIM removes extra spaces, while CLEAN removes non-printable ASCII characters. Neither function understands whether text is a street, customer note or apartment instruction. Review before filling formulas down.
=TRIM(CLEAN(A2))
=TEXTJOIN(", ", TRUE, B2:F2)
=REGEXREPLACE(A2, "\s+", " ")
=SUBSTITUTE(A2, CHAR(160), " ")TEXTJOIN is useful when fields are separated: street, city, region, postal code and country can be combined while blank components are ignored. Paste values only after verifying the output if you need to remove formula dependencies.
Separate text that is not part of the address
- Customer names and account numbers.
- Phone numbers and email addresses.
- Gate codes, delivery windows and access instructions.
- Comments such as 'behind the blue house' or 'call on arrival.'
- Multiple candidate addresses in one cell.
- Internal region abbreviations that a public geocoder cannot interpret.
Do not force every country into one address pattern
Street order, postal-code placement and administrative levels vary. Store country explicitly and preserve local characters. Transliteration can be a secondary search aid, but replacing the original script removes information and may create new ambiguity.
| Issue | Safer treatment |
|---|---|
| Leading-zero postal code | Store as text |
| Local script | Preserve it; optionally add a separate transliteration |
| Unknown state equivalent | Use the actual administrative area rather than inventing a state |
| Country abbreviation | Prefer an unambiguous country name or standard code |
| Building or district name | Keep it when locally significant |
Pilot the cleaning rules before paying for the full batch
Select examples from every source system and country, including known problem rows. Compare raw and cleaned outcomes by status and geographic plausibility. A cleaning rule is successful only if it improves the intended records without damaging already-good inputs.
Sources and methodology
Technical claims were checked against the sources below on August 18, 2026. Product limits and third-party pricing can change; verify the live documentation before designing a production workflow.
Common questions
Should an address be in one column or several columns?
Either works. A combined address is simple to select, while separated street, city, region, postal code and country fields are easier to validate. Sheets Geocoder can join adjacent selected fields from left to right.
Should apartment numbers be removed?
Keep a unit only when it helps identify the intended location, but do not expect most geocoders to return a unit-level point. Put delivery instructions and access notes in separate columns.
Why did a postal code lose its leading zero?
Sheets interpreted it as a number. Format postal-code columns as plain text before import or entry so values such as 02108 remain intact.
Should addresses be converted to uppercase?
Usually not. Case rarely improves matching and can make the data less readable. Completeness and geographic context matter more than capitalization.