How to Clean Up Addresses in Excel Using Formulas

To clean up addresses in Excel, the most efficient method is to import your spreadsheet into Google Sheets and apply the free InstaMaps add-on. The =CLEAN_ADDRESS(A2) formula standardises street formats, fixes capitalisation, and removes extra spaces, instantly turning messy text into usable data.

This guide is for sales representatives, delivery planners, and real estate analysts who are tired of manually fixing thousands of inconsistently formatted rows. By the end of this tutorial, you will have a completely automated pipeline that takes your raw Microsoft Excel exports, structures them properly, and generates a live map of your locations.

What you need before you start

Address data exported from CRMs or typed manually into Microsoft Excel frequently contains trailing spaces, incorrect capitalisation, and combined fields. While Microsoft Excel provides basic text functions like PROPER or TRIM, it lacks a dedicated engine to accurately parse and standardise geographical information.

To resolve this, you can import your Excel file into Google Sheets. Go to File > Import > Upload and select your .xlsx or .csv file. Once your data is open in Google Sheets, install the free InstaMaps add-on from the Google Workspace Marketplace. After installation, launch the tool by navigating to Extensions > InstaMaps > Formulas. This opens a helpful sidebar that allows you to insert location formulas into your cells without any typing required.

Step 1: Standardise formatting with CLEAN_ADDRESS

The first step in organising your data is correcting inconsistent text formatting. Users often type addresses in all capital letters, all lowercase, or a mix of both, which makes mail merges, shipping labels, and official documentation look entirely unprofessional.

Instead of combining multiple text formulas, you can use the InstaMaps add-on to apply a dedicated formatting function. In an empty column directly next to your raw addresses, apply the address formula. If your raw, unformatted address is located in cell A2, you would input the following into cell B2:

1. Formula: =CLEAN_ADDRESS(A2)

2. If cell A2 contains '123 mAIN st', the formula returns '123 Main St'.

3. If cell A2 contains '45 elm road', the formula returns '45 Elm Road' by deleting the extra spaces.

Step 2: Isolate specific components with POSTCODE

Often, a single address column contains the entire location string, including the street number, city, and postal code. If you need to sort your data by geographic region, verify delivery zones, or filter by specific neighbourhoods, you must isolate the postal code into its own separate column.

Typing complex LEFT, RIGHT, and FIND formulas in Excel to extract postal codes is tedious and breaks easily when address lengths vary. The InstaMaps add-on handles this text variation instantly. In cell C2, you can extract the postcode from your raw address column by using the following formula:

1. Formula: =POSTCODE(A2)

2. If cell A2 contains '123 Main St, London SW1A 1AA', the formula returns 'SW1A 1AA'.

3. This allows you to quickly pivot or filter your spreadsheet by specific postal areas.

Step 3: Generate a shareable map with INSTAMAP

Once your addresses are cleaned and your postal codes are isolated, viewing them visually on a map is the logical next step for planning routes or analysing territories. The InstaMaps add-on creates a hosted map URL directly inside your spreadsheet interface.

Apply the mapping formula to your cleaned data range. If your newly cleaned addresses are located in column B, spanning from row 2 to row 50, you would input the following formula into an empty cell:

1. Formula: =INSTAMAP(B2:B50)

2. Result: The cell outputs a live, hosted shareable map URL that updates automatically when you add or change addresses in your sheet.

Worked example: A 200-home real estate farm

Consider a real estate agent managing a local farm of 200 homes. The agent receives a Microsoft Excel spreadsheet from a local title company, but the data is messy: some addresses are typed in lowercase, others contain double spaces, and the postal code is merged directly with the city name.

The agent imports the Excel file into Google Sheets. In column B, they run =CLEAN_ADDRESS(A2:A201) to fix the capitalisation and spacing across all 200 homes. In column C, they run =POSTCODE(A2:A201) to isolate the postal codes, allowing them to filter their farm by specific delivery routes.

Finally, the agent uses =INSTAMAP(B2:B201) to generate a single URL. They share this link with their team so everyone can view the exact locations of the 200 homes on a live, interactive map during their weekly strategy meetings.

Limits and honest alternatives

InstaMaps provides a free tier of 100 lookups per day. If you create a free account using your email address, this daily limit increases to 1,000 lookups per day at no cost. The add-on itself is entirely free to use.

If you have a static database of 50,000 addresses that needs immediate bulk processing in a single afternoon, a paid enterprise CRM or dedicated deduplication software might be a better fit for that specific one-time task, as our tool is designed for ongoing daily operations rather than massive one-off batch processing. However, for standard daily operations, ongoing list management, and generating live maps, the add-on handles the workload without requiring a paid subscription.

Common Questions