Split a Full Address Into Street, City, State and Zip Columns

To split an address into columns, read it from the end: the country if there is one, then the postal code by its shape, then the state, then the city; what's left is the street, with any unit moved to its own column. The split address into columns tool does that for US, Canadian, Australian and UK addresses in your browser, keeps your other columns, and flags rows it wasn't sure about.

Source: USPS Publication 28, Postal Addressing Standards, including the street suffix abbreviations, read 3 November 2026.

Why Text to Columns fails

500 Oak Ave, Suite 200, Portland, OR 97204 splits on commas into four columns; 42 Elm Street Boston MA 02108 stays in one. Either way, state and zip share a cell, and Excel drops the zero from 02108. Formulas that take the last word for the zip work until one row has a ZIP+4, a country on the end or a unit line.

How reading from the end works

Step	Looks for	Example
1	Country name or code (optional)	USA, Canada
2	Postal code by shape	97204, 97204-1234, M5V 2T6, SW1A 2AA, 3000
3	State or province by name or code	OR, Oregon, ON, NSW
4	City	Portland
5	Unit designator, moved out	Suite 200, Apt 4B, #12
6	The rest is the street	500 Oak Ave

Without commas, the street is cut after its last street type (St, Ave, Rd, Blvd, Lane, Way and about 50 more, from the USPS suffix list) and an optional direction such as NW. 42 Elm Street Apt 4B Boston MA 02108 splits cleanly.

Steps

1. Paste one address per line, or upload a CSV. The column named address, or the one that looks like addresses, is used.
2. Pick the country most addresses are in. Others are still read and get their country filled in.
3. Press Split addresses and read the Check column for rows it had to guess.
4. Fix those few by hand, then download the CSV with street, unit, city, state, postal_code, country and check columns added.

States come out as two- or three-letter codes; the state abbreviation converter switches to full names if your import wants them, and normalize country and state columns covers mixed spellings. If the name is in one cell too, the split full name tool does the same job for first and last names.

Open the result without losing zeros

The CSV keeps postal codes as text. Excel won't, on a double-click. Convert it with CSV to Excel or follow open a CSV in Excel without losing leading zeros before anyone saves it.

Limits

Only US, Canada, Australia and UK formats are parsed; others are split by commas where possible and flagged. A street with no street type and no comma runs into the city, and those rows are flagged rather than guessed. The tool doesn't look addresses up, correct spellings, add ZIP+4 or standardise against postal data such as USPS CASS. And it doesn't check the email addresses on the same rows.

Where SimpleVerifier fits

If the list has email addresses too, upload the split file at /verify: every address gets a live mailbox check, and valid, risky and invalid download as separate files with your address columns kept. Mail merge blank fields covers the rows that come back without a city or zip. The first 100 checks are free with a Google sign-in. Pro is $39 a month with the first 100,000 checks a month at full speed, then slower, never cut off. See pricing.