Fuzzy Address Matching: How to Match Addresses in Google Sheets

At a Glance: The Short Answer

Fuzzy address matching is the process of linking addresses that refer to the same location but are written differently. "123 Main St", "123 Main Street" and "123 MAIN STREET, Apt 4" all describe the same building, but no exact match will find them.

The reliable approach has two parts. First, normalise and standardise every address so consistent components are comparable. Second, apply fuzzy matching to the normalised fields, comparing street name, number, unit and postcode, and scoring how close the records are.

Done this way, fuzzy address matching catches typos, abbreviations and formatting differences without merging addresses that only look alike but are actually different places.

Why Address Matching Is Hard

Addresses combine numbers and words, which makes them different from names or descriptions:

• Abbreviations: St vs Street, Rd vs Road, Ave vs Avenue, Apt vs Apartment.
• Order: "Main Street 123" vs "123 Main Street".
• Casing and punctuation: "MAIN ST" vs "Main St." vs "Main Street".
• Missing parts: some records lack unit numbers, postcodes or city names.
• Typos: a misspelled street name that changes the string but not the place.

These variations mean a whole-string comparison produces false negatives, two copies of the same address scored as different. The solution is to break addresses into components and compare those.

Normalise and Standardise First

Before any fuzzy matching, put every address in a consistent form. Standard normalisation steps:

• Lowercase everything and trim whitespace.
• Expand or standardise common abbreviations, St, Rd, Ave, Apt.
• Standardise directionals like "N" vs "North" and unit formats.
• Normalise postcodes and zip formats.
• Split the address into component columns, street, number, unit, city, region, postcode.

Flookup's Text Standardisation handles the abbreviation and punctuation work. Standardising phone numbers demonstrates the same pattern for another field type.

Match by Component, Not Whole String

Once addresses are split into components, matching becomes far more reliable. Compare the street name with fuzzy similarity, the street number exactly, the postcode exactly and the city exactly. A record is a match when the core components agree.

This structure prevents false positives. Two addresses on "Main Street" in different cities share the same street name but fail the city and postcode checks. Comparing the full string would have scored them close; comparing components separates them correctly.

Component matching is why address matching tools perform better than a generic fuzzy string compare, they understand the parts of an address.

The Address Matching Workflow

1. Profile the address columns to see which components are present and where the variation is.
2. Standardise casing, punctuation and abbreviations.
3. Split addresses into street, number, unit, city, region and postcode columns.
4. Match each component with the appropriate comparison, exact for numbers and postcodes, fuzzy for street names.
5. Score the record with a confidence value and set a threshold for acceptance.
6. Review the low-confidence matches rather than accepting everything.

Address Matching in Google Sheets

Address lists are frequently maintained in spreadsheets, and Google Sheets is a practical place to clean and match them. Flookup Data Wrangler provides the pieces:

• Text Standardisation to normalise casing, punctuation and abbreviations.
• Fuzzy Match to compare address fields against a reference list and return the closest match with a score.
• Smart Deduplicate to group near-duplicate address records inside one sheet.

For a shipping list, a CRM export or a merged database, this turns scattered address variations into one clean set you can trust.

Common Pitfalls to Avoid

• Matching before normalising: skipping the clean step makes similarity scores meaningless.
• Whole-string comparison: ignoring address structure causes both false matches and missed matches.
• Accepting everything: without a confidence threshold, near-misses become bad merges.
• Ignoring postcodes: two places can share a street name, the postcode is what separates them.

Final Thoughts

Fuzzy address matching works when you treat addresses as structured data, not as free text. Normalise, split into components, match each part appropriately and gate the result on a confidence score. That combination catches real duplicates and leaves genuine differences alone.

In Google Sheets, Flookup Data Wrangler automates the normalisation and fuzzy matching steps. Try it free from the Google Workspace Marketplace and clean your address list in minutes.