Parse and Clean Messy Address Columns in Excel

You have a spreadsheet with addresses crammed into one column, or split awkwardly across five columns, and you need them clean before a mail merge, a CRM import, or a geocoding job. Excel formulas can handle predictable patterns, but real address data is rarely predictable. You get inconsistent abbreviations (St, Street, STR), missing ZIP codes, apartment numbers glued to street names, country names in some rows but not others, and the occasional phone number dropped in by accident.

This page shows how to use an AI prompt to parse and standardize messy address columns into structured fields (street, unit, city, state, postal code, country). You paste the prompt, feed it your column, and get back a clean table you can drop straight into Excel. Below you will find the tested prompt, outputs from four models compared side by side, plus guidance on when this beats formulas or Power Query, and where it falls short.

When to use this

Use an AI prompt when addresses are inconsistent enough that no single formula or delimiter works. If you have mixed formats, free-text entries, or multi-country data, AI handles the judgment calls (is '100 N Main' a street or a building name?) that regex cannot. It is ideal for one-off cleanups or batches under a few thousand rows.

• Cleaning a CRM export before importing into HubSpot, Salesforce, or Mailchimp
• Preparing a mailing list where some rows have full addresses and others have just 'NYC office'
• Standardizing vendor or customer address fields collected from web forms with no validation
• Splitting a single 'Address' column with 5,000 rows into street, city, state, ZIP for a pivot report
• Normalizing international addresses that mix US, UK, and EU formats

When this format breaks down

• Datasets over 50,000 rows where token costs and latency exceed the value of cleaning. Use Power Query or a dedicated address API like Smarty or Google Geocoding.
• When you need verified deliverability (real USPS-validated addresses). AI will confidently reformat a nonexistent address. Use an address verification service.
• When the data is already well structured and just needs casing or trimming. A simple PROPER(), TRIM(), or Flash Fill does it faster.
• Regulated data (HIPAA, patient records) where you cannot send raw addresses to a third-party model without a BAA in place.

What makes these work

1. 01
   Specify the output columns

   Tell the model exactly which fields you want: Street, Unit, City, State, ZIP, Country. If you leave it open, different rows get different schemas and the paste into Excel breaks. Lock the column order in the prompt.

2. 02
   Give it a format example

   One or two input-output pairs in the prompt anchor the abbreviation style (St vs Street, CA vs California). Without an example the model picks its own convention and you spend time normalizing the normalization.

3. 03
   Handle missing data explicitly

   Instruct the model to leave cells blank rather than guess. Otherwise it will hallucinate ZIP codes or assume 'US' for a UK address. Add: 'If a field is not present in the source, leave it empty.'

4. 04
   Batch in chunks of 50-100 rows

   Large pastes cause models to drop rows or truncate. Feed the data in blocks, paste results into Excel, and repeat. Keep a row count check so you catch dropped records.

More example scenarios

123 main st apt 4b, brooklyn ny 11201
456 Oak Avenue Suite 200 Chicago IL 60601
789 elm rd, austin tx
1000 Market St. #501, San Francisco, California 94103 USA

| Street | Unit | City | State | ZIP | Country |
|---|---|---|---|---|---|
| 123 Main St | Apt 4B | Brooklyn | NY | 11201 | US |
| 456 Oak Ave | Suite 200 | Chicago | IL | 60601 | US |
| 789 Elm Rd | | Austin | TX | | US |
| 1000 Market St | #501 | San Francisco | CA | 94103 | US |

Acme Ltd, 10 Downing Street, London SW1A 2AA, United Kingdom
muller gmbh // hauptstrasse 15 // 10115 berlin germany
Sakura Corp, 1-2-3 Shibuya, Tokyo 150-0002, JP

| Company | Street | City | Postal Code | Country |
|---|---|---|---|---|
| Acme Ltd | 10 Downing Street | London | SW1A 2AA | United Kingdom |
| Muller GmbH | Hauptstrasse 15 | Berlin | 10115 | Germany |
| Sakura Corp | 1-2-3 Shibuya | Tokyo | 150-0002 | Japan |

John Smith - 22 pine street apt2, boston, ma 02108 - phone 617-555-0100
sarah lee | PO BOX 445 seattle wa | sarah@email.com
Dr. R. Patel, 5th floor 88 King's Cross Rd London WC1X 9DE

| Name | Street | Unit | City | State/Region | Postal Code |
|---|---|---|---|---|---|
| John Smith | 22 Pine St | Apt 2 | Boston | MA | 02108 |
| Sarah Lee | PO Box 445 | | Seattle | WA | |
| Dr. R. Patel | 88 King's Cross Rd | 5th Floor | London | | WC1X 9DE |

1420 Ocean Blvd Unit 7, Miami FL 33139
1420 ocean blvd #7 miami fl 33139
1420 OCEAN BOULEVARD APARTMENT 7 MIAMI FLORIDA 33139

All three rows normalize to the same canonical record:
| Street | Unit | City | State | ZIP |
|---|---|---|---|---|
| 1420 Ocean Blvd | Unit 7 | Miami | FL | 33139 |
This makes deduplication trivial with a COUNTIFS on the combined fields.

ship to: warehouse 3, 4500 industrial pkwy bldg c dock 12, fort worth tx 76106
consignee- smithco, 99 cedar ln ste 400, atlanta ga 30303

| Recipient | Street | Building/Unit | City | State | ZIP |
|---|---|---|---|---|---|
| Warehouse 3 | 4500 Industrial Pkwy | Bldg C Dock 12 | Fort Worth | TX | 76106 |
| Smithco | 99 Cedar Ln | Ste 400 | Atlanta | GA | 30303 |

Common mistakes to avoid

• Trusting the ZIP code output

  AI will invent a plausible ZIP when the source is missing one. Always diff the output against the input for any field the model 'completed'. Flag rows where the output has a value the input did not.

• Mixing countries in one pass

  Asking for a single US-style schema on international data mangles postal codes and collapses regions into the State column. Split by country first, or include a Country column and let the model use region-appropriate fields.

• Skipping the TRIM and CLEAN step

  Non-breaking spaces and invisible characters from copy-paste confuse the model and the downstream VLOOKUP. Run TRIM(CLEAN()) on the column before sending, or ask the model to strip them explicitly.

• No dedupe check after cleaning

  Cleaning exposes duplicates that looked different before (e.g., 'St' vs 'Street'). If you skip the dedupe pass after normalization, you import the same record twice into your CRM.

• Pasting as plain text into Excel

  Markdown tables paste as a single column. Use Data > Text to Columns with pipe delimiter, or ask the model for tab-separated output so Excel splits it automatically.

Related queries