Use the Text to Columns feature to split a full address into street, city, state, and zip

Excel can break a full address into separate columns automatically if the address parts are separated by commas or spaces. The Text to Columns tool reads a delimiter — the character that separates each part — and splits the data across multiple columns. If your addresses look like "123 Main St, Springfield, IL 62701", you can split them into four columns in under a minute.

The process works best when your addresses follow a consistent format. If some addresses have commas between parts and others don't, or if street names contain commas, you may need to clean up the data first or use a formula instead. For most standard address lists, Text to Columns is the fastest route.

Key Takeaways

  • Text to Columns splits addresses at commas, spaces, or other delimiters you choose, placing each part in its own column.
  • Your original address column will be replaced by the split data, so copy it to a new column first if you need to keep the full address.
  • Addresses must follow a consistent format — all with commas between parts, or all with the same spacing — for Text to Columns to work cleanly.
  • If addresses don't split evenly or contain inconsistent formatting, a formula like MID, FIND, or TEXTSPLIT (Excel 365 only) may work better.

How to split addresses using Text to Columns

Start by selecting the column that contains your full addresses. Click the column letter at the top to select the entire column, or click the first cell with an address and drag down to select only the cells you need. Then go to the Data menu at the top and click Text to Columns.

A dialog box will open. In the first step, choose Delimited (not Fixed Width) and click Next. On the second screen, check the box next to the delimiter that separates your address parts. If your addresses use commas, check Comma. If they use spaces, check Space. You can check more than one if your data mixes delimiters. Click Next again.

The third screen shows a preview of how your data will split. Look at the preview to make sure each address part lands in its own column. If it looks right, click Finish. Excel will replace your original column with the split data, placing street address in the first new column, city in the second, state in the third, and zip code in the fourth.

Prepare your data before splitting

Before you use Text to Columns, check that all your addresses follow the same format. Open your spreadsheet and scroll through several rows. Look for addresses that have extra spaces, missing commas, or parts in a different order. If you find inconsistencies, fix them first — Text to Columns works reliably only when the data is uniform.

Copy your address column to a new location before you split it. Text to Columns overwrites your original data, so if you need to keep the full address for reference or for other uses, paste a copy into an empty column first. Select the address column, press Ctrl+C (or Cmd+C on Mac), click an empty column to the right, and press Ctrl+V. Then run Text to Columns on the copy.

If your addresses contain extra spaces between parts — for example, "123 Main St, Springfield" with two spaces after the comma — Text to Columns may create empty columns. To clean this up, use Find & Replace. Press Ctrl+H, type two spaces in the Find field, type one space in the Replace field, and click Replace All. This removes the extra spaces so Text to Columns splits cleanly.

Use formulas when Text to Columns won't work

If your addresses don't follow a consistent format, or if some contain commas within the street address itself, formulas give you more control. In Excel 365, the TEXTSPLIT function is the simplest option. In the cell where you want the first part of the address to appear, type =TEXTSPLIT(A1,","), replacing A1 with the cell containing the full address and "," with your delimiter. Press Enter, and Excel will split the address across columns automatically.

In older versions of Excel, use MID and FIND together. To extract the street address from a cell, type =LEFT(A1,FIND(",",A1)-1). This finds the first comma and takes everything before it. To extract the city, use =MID(A1,FIND(",",A1)+2,FIND(",",A1,FIND(",",A1)+1)-FIND(",",A1)-2). These formulas are longer, but they handle inconsistent spacing and let you skip parts you don't need.

Formulas create new data in separate cells rather than replacing your original column, so your full address stays intact. Once the formula works, you can copy it down to all rows. If you want to convert the results back to plain values (so they don't change if the original address changes), select the cells with formulas, press Ctrl+C, right-click, choose Paste Special, and click Values.

Clean up and verify the split data

After splitting, check the first few rows to make sure each part landed in the right column. Look for addresses where the street number or name got cut off, or where the city and state ended up in the wrong columns. If you see errors, undo the split by pressing Ctrl+Z, fix the original data, and try again.

Add headers to your new columns so you know what each one contains. Click the cell above the first split column and type "Street Address". Move to the next column and type "City", then "State", then "Zip Code". Headers make it easier to sort, filter, or reference the data later.

If any cells are blank or contain unexpected text, fix them manually. A blank cell usually means that address didn't have all the parts — for example, a P.O. box without a street number. A cell with extra text might mean the original address had an apartment number or suite that didn't fit the standard format. These are usually quick fixes and won't affect the rest of your data.

When to use each method

Use Text to Columns if all your addresses follow the same format with consistent delimiters. It is the fastest method and requires no formulas. This works well for addresses downloaded from a database or form that enforces a standard format.

Use TEXTSPLIT (Excel 365 only) if you want to keep your original address and don't need to overwrite the source data. TEXTSPLIT is simpler than older formula methods and handles most variations in spacing automatically.

Use MID and FIND formulas if you have older Excel and need to extract specific parts while keeping the original address intact. These formulas take longer to write but give you precise control over which parts you extract and where they go.

Frequently Asked Questions

Will Text to Columns work if my addresses don't have commas?

Yes, if the parts are separated by spaces or another consistent character. You can choose space as the delimiter instead of comma. However, if your street address contains multiple words — like "123 Main Street" — using space as a delimiter will split the street address itself into separate columns. In that case, a formula method works better.

Can I undo Text to Columns if I make a mistake?

Yes. Press Ctrl+Z when ready after running Text to Columns to restore your original data. If you have already done other work since then, undo will go back through each step. This is why copying your address column before splitting is a good safety step.

What if some of my addresses are missing a city or state?

Text to Columns will still split at the delimiters, but the missing part will leave a blank cell. After splitting, you can sort or filter by blank cells to find incomplete addresses and fill them in manually. Formulas like TEXTSPLIT handle missing parts the same way.

How do I split addresses that have apartment or suite numbers?

If the apartment number is separated by a comma — like "123 Main St, Apt 4, Springfield, IL 62701" — Text to Columns will put it in its own column. You can then move it back into the street address column or keep it separate depending on your needs. If the apartment number is part of the street address with no comma, it will stay with the street and won't split.

Can I split addresses in place without losing the original data?

Text to Columns overwrites the original column, so copy your addresses to a new column first. Formulas like TEXTSPLIT or MID/FIND create the split data in new cells, leaving your original addresses untouched. Use formulas if you need to preserve the full address for reference or other uses.