To clean up addresses in Google Sheets, install the free InstaMaps add-on and use the =CLEAN_ADDRESS(A2) formula on your target cell. This function standardises capitalisation, removes redundant spaces, and corrects formatting issues, returning a properly structured postal address ready for mapping or mailing.
This reference guide is for sales teams, real estate analysts, and logistics coordinators dealing with CRM exports or survey data. By the end, you will have a reliable method to format raw text into standardised locations, serving as the first link in your data preparation chain before calculating distances or building live maps.
- →Use the =CLEAN_ADDRESS() formula from the InstaMaps add-on to format messy text.
- →Standardises capitalisation, removes redundant spacing, and structures location components.
- →Serves as the mandatory first step before running geocoding or distance formulas.
- →Composes directly with =GEOCODE() and =INSTAMAP() to automate your workflow.
- →Prevents mapping errors caused by missing commas or inconsistent data entry.
- →Free tier includes 100 formula lookups per day (1,000 with a free email unlock).
What you need before you start
To use this formula, you need a Google Sheet containing your raw location data and the InstaMaps add-on installed on your browser. InstaMaps is a free Google Workspace add-on that provides custom spreadsheet formulas for location data.
If you prefer not to type syntax manually, you can access the point-and-click interface by navigating to Extensions > InstaMaps > Formulas in your top menu. This opens a sidebar that inserts the formula directly into your active cell.
Step 1: Understand the CLEAN_ADDRESS syntax
The function follows a simple structure designed to process text strings into predictable outputs. It evaluates the input against known formatting rules.
Syntax: =CLEAN_ADDRESS(location)
location (required): The cell reference or text string containing the messy address data you want to standardise.
Step 2: Apply the formula to your sheet
Highlight the cell where you want the tidy output to appear. If your raw address is in cell A2, type the following formula: =CLEAN_ADDRESS(A2). Press Enter.
The sheet will immediately output a corrected version. For example, if A2 contains "123 maIN street, london e1 6an", the formula returns "123 Main Street, London E1 6AN". You can drag the fill handle down to apply this to an entire column of leads or properties.
Step 3: Compose CLEAN_ADDRESS with other functions
InstaMaps formulas are designed to compose (nest) inside one another. Because =CLEAN_ADDRESS() returns a reliable, properly formatted text string, it prevents downstream mapping formulas from failing on bad inputs.
Chain 1: You can nest this inside a geocoding function to get coordinates without an #ERROR! result. Use =GEOCODE(CLEAN_ADDRESS(A2)).
Chain 2: You can pass the cleaned data directly into a mapping function to generate a shareable link. Use =INSTAMAP(CLEAN_ADDRESS(A2:A50)) to return a live hosted map URL that updates when your sheet changes.
Worked example: A 200-home property portfolio
Consider an estate agency managing a 200-home portfolio. The properties are exported from a legacy CRM into a Google Sheet, but the addresses contain mixed casing, missing commas, and extra spaces typed by different agents over the years.
By placing =CLEAN_ADDRESS(A2:A201) in column B, the analyst creates a standardised list in seconds. This clean output is then used to run =SORT_BY_DISTANCE() against their central office, allowing them to assign maintenance crews efficiently without manual data scrubbing.
Limits and common errors
If you see a #NAME? error in your cell, it means Google Sheets cannot find the formula. Ensure the InstaMaps add-on is installed and activated for the specific document. You can read our internal name-error post for troubleshooting steps.
InstaMaps caps usage to ensure server stability. The free tier allows 100 formula lookups per day. You can increase this to 1,000 lookups per day with a free email unlock. If you are processing massive lists of 50,000 rows or more, a dedicated paid enterprise geocoding tool may be a better fit for your workload.
Map your Salesforce accounts in under 5 minutes — no admin setup.
Common Questions
You can pass an entire range into the formula instead of a single cell. For example, typing =CLEAN_ADDRESS(A2:A200) will process the array and return a corresponding column of standardised addresses.
This error appears when Google Sheets does not recognise the function name. Ensure you have the InstaMaps add-on installed from the Google Workspace Marketplace and that you are logged into your Google account.
No. The function standardises the formatting of the text provided, such as fixing capitalisation and removing extra spaces. It does not verify whether the postal code actually matches the physical street.
No. Because the add-on relies on external mapping databases to correctly parse and structure complex address components, it requires an active internet connection to return results.
Yes. The free tier allows 100 lookups per day. Registering with a free email unlock increases this capacity to 1,000 lookups per day.
Stop manually editing CRM exports and survey data. Install InstaMaps to clean your addresses, geocode coordinates, and build live maps directly inside your spreadsheet.
Install InstaMaps free