How to Separate an Address in Excel (3 Methods)

Method 1 – Using the ‘Text to Columns’ Option to Split Addresses

The below dataset shows an address 986 Riverview Ct-Xenia, OH, 45385’ in the B5 cell from which we want to separate Street, City, State, ZIP Code to the following cells.

STEPS:

Select the column where the full address is described and copy the full address in the adjacent column.

Go to the Data tab and then choose the ‘Text to Columns’ option.

This will take us to the Convert Text to Columns Wizard Step 1 window.

Select Delimited to separate the specific portion, which can be commas, tabs, hyphens, or spaces.
The selected value is in the Preview section.
Click the Next button.
A Convert Text to Columns Wizard Step 2 window pops up.
If the addresses in your file are separated by commas and hyphens, select Comma and hyphen in the Delimiter section and see the separated value in the Preview section.
Press Next.

In the Convert Text to Columns Wizard Step 3, select Column Data format as General.
Select the Destination as $C$5.
You will get a Data preview where the separation as per command is shown.
Press Finish to get the result.

Name the column headers such as Streets, City, State, and Zip Code.
The result will be like the below image:

Method 2 – Using the Flash Fill Feature

STEPS:

Fill the first cell (Cell C5, D5, E5, F5) according to the pattern of information that we want in the Columns consecutively.

Go to the Data tab and choose the ‘Flash Fill’ option.

Fill column C containing a Street address, column D with City names, column E with State, and column F with ZIP Code.
The Flash Fill feature will fill in the rest based on the pattern provided in the first row. (In the above figure, we selected Street.)
Click the Flash Fill option from the Data tab to get ‘Street Addresses’ from the full address.