Splitting a Full Address into Street, City, State, Zip

You export a customer list and every address is crammed into a single column: "123 Main St, Springfield, IL 62701". One big string. You need to filter by state, or group by city, or send the zip codes to a mailing service. None of that works when everything is glued together.

This is one of the most common data cleaning tasks. CRM exports, e-commerce platforms, and form submissions all love stuffing addresses into one field. The fix takes about two minutes in ExploreMyData.

What we're starting with

A typical address column looks something like this:

• 123 Main St, Springfield, IL 62701
• 456 Oak Ave, Chicago, IL 60601
• 789 Pine Rd, Austin, TX 78701
• 42 Elm St, Portland, OR 97201

The pattern is consistent: street, comma, city, comma, state and zip. That comma delimiter is your best friend here.

Step 1: Split the address by comma

Open the Split Column operation from the Columns group. Select your address column and set the delimiter to , (comma). Set the number of parts to 3.

This creates three new columns:

• address_1 - the street ("123 Main St")
• address_2 - the city (" Springfield")
• address_3 - the state and zip (" IL 62701")

Under the hood, ExploreMyData generates:

SPLIT_PART(address, ',', 1) AS address_1
SPLIT_PART(address, ',', 2) AS address_2
SPLIT_PART(address, ',', 3) AS address_3

Two problems are immediately obvious. The city has a leading space. And the state and zip are still stuck together.

Step 2: Trim whitespace from city

Select Text Transform from the Transform group. Pick the address_2 column and choose "trim". That leading space disappears. " Springfield" becomes "Springfield".

The SQL is straightforward: TRIM(address_2). The step rewrites the column in place, so there's no second copy to clean up. Do the same trim on address_3 while you're here, since it has the same leading space and step 3 needs it gone.

Renaming comes later, in one go. There's a Rename Columns operation in the Columns group; you don't need the copy-then-delete dance.

Step 3: Extract state and zip

After the trim, address_3 holds "IL 62701". Two Extract Text steps pull it apart, each writing to a new column.

For the state: extraction method before_delimiter, delimiter a single space, output name state. That gives you "IL", and the SQL is the one you'd expect:

SPLIT_PART(address_3, ' ', 1) AS state

For the zip: method after_delimiter, same delimiter, output name zip. Here the SQL is not the mirror image you might assume:

CASE WHEN POSITION(' ' IN address_3) > 0 THEN SUBSTRING(address_3, POSITION(' ' IN address_3) + LENGTH(' ')) ELSE '' END AS zip

The two methods are built differently on purpose. "before" can use SPLIT_PART because part 1 is well defined. "after" takes everything past the first delimiter rather than just the next segment, so a value with two spaces in it keeps the tail intact instead of losing it. The ELSE '' is what you get when the delimiter isn't there at all: an empty string, not a NULL. Filter zip with is Empty afterwards to find the rows where address_3 had no space.

A note on ZIP+4. "IL 62701-1234" splits fine, and zip comes out as "62701-1234" because everything after the space is kept. Two things follow from that. Don't Convert Type the zip column to numeric: the hyphenated ones would go blank, and every Massachusetts and New Jersey zip would lose its leading zero and turn 02101 into 2101. And if you need the five-digit form for a mailing service, add one more Extract Text on zip with before_delimiter and a hyphen. That leaves plain five-digit zips untouched, since SPLIT_PART returns the whole string when the delimiter is absent.

Step 4: Rename the columns

You're left with address_1, address_2, address_3, state and zip. Add Rename Columns from the Columns group and map address_1 to street and address_2 to city in a single step. Columns you don't list are passed through untouched, so you only fill in the two you care about.

Then Delete Columns to drop address_3 and, if you don't need it, the original address.

The result

Seven pipeline steps in total, and your single address column is now four clean columns: street, city, state, and zip. Each one is independently filterable, sortable, and groupable.

Want to see how many customers you have in each state? Group by the state column. Need to filter to just Texas zip codes starting with 787? Filter the zip column with "starts with". Need to export city and state for a geocoding API? Select just those columns.

Handling messy real-world addresses

Not every address follows the neat "street, city, state zip" pattern. Some things to watch for:

• Apartment numbers: "123 Main St Apt 4B, Springfield, IL 62701" - the street part includes the unit. That's usually fine since it stays in the street column.
• Missing commas: "123 Main St Springfield IL 62701" - without commas, splitting by comma won't work. You might need to use Extract Text with position-based extraction or regex.
• Extra fields: "123 Main St, Suite 200, Springfield, IL 62701" - four comma-separated parts instead of three. Set the split to 4 parts and combine the first two for the full street address.

The key is to look at your data first. Scroll through the address column and spot the pattern. Most datasets from a single source follow a consistent format, even if it's not the textbook one.

Why not do this in a spreadsheet?

You absolutely can. Excel has Text to Columns, Google Sheets has SPLIT(). But there are two advantages to doing it here. First, the split is a pipeline step rather than a one-time edit, so the next export from the same CRM gets the same treatment without you touching anything. Second, you can see the SQL that runs. If you need to hand this off to someone building a proper ETL pipeline, they have the exact DuckDB query.

Try splitting your address data →