How to Separate Address in Excel (3 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will show how to separate different parts of a full address in Excel. At times, renowned Companies or users need only specific information rather than full information of a customer or supplier address for project work or storage of information. Sometimes, we may need to collect only the Street Number instead of a full address. From a list of addresses filled with unnecessary information, if we want to separate them up with only the useful information needed, there are very flexible yet highly effective features in Excel.

Table of Contents hide

How to Separate Address in Excel: 3 Effective Ways

1. Use ‘Text to Columns’ Option to Split Address in Excel

2. Separate Address into Different Columns with Flash Fill Feature

3. Apply Excel LEFT, RIGHT and MID Functions to Separate Address

Conclusion

1. Use ‘Text to Columns’ Option to Split Address in Excel

Let’s say that the below figure 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:

First, we have to select the column where the full address is described and copy the full address in the adjacent column.

After that, we have to 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.
Now, we can see the selected value in the Preview section.
Click the Next button.
After clicking Next, a Convert Text to Columns Wizard Step 2 window pops up.
If the addresses in your file are separated by commas and hyphens, you should 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.

The final step is naming the column headers such as Streets, City, State, and Zip Code.
In the end, the result will be like the below image:

2. Separate Address into Different Columns with Flash Fill Feature

Excel’s Flash Fill feature is one of the most effective yet simple methods to separate specific information from a string of full information. Here we will show you how to use the Flash Fill feature to separate addresses in Excel. From a list of full addresses like the figure below, we can separate the information we want in desired columns C, D, E, and F accordingly.

STEPS:

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

After that, we have to go to the Data tab and then choose the ‘Flash Fill’ option.

Let’s say you want to 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.
Later, click the Flash Fill option from the Data tab to get ‘Street Addresses’ from the full address.

Now fill Row D5 with the City Name which is Xenia from B5 where the full address is located.
Select City in D4.

Now click the Flash Fill option.
The figure below shows the result of getting all the ‘City’ names using the ‘Flash Fill’ option.

Using the same method of Flash Fill, you can get all ‘State’ and ‘ZIP Code’ values.
The final output will look like the figure below:

Similar Readings

3. Apply Excel LEFT, RIGHT and MID Functions to Separate Address

In this method, we will use Excel to separate specific information from full information using LEFT, MID & RIGHT functions in the below dataset to separate the address. we use the required argument for that certain cell to get the desired result.

STEPS:

Firstly, select cell C5.
Next, write the below formula and press Enter to see the result:

=LEFT(B5,16)

Secondly, select cell F5.
Now, write the below formula.

=RIGHT(B5,5)

Press Enter to see the result.

Thirdly, select cell D5.
Then, write the formula below:

=MID(B5,18,5)

Press Enter to get City Name.

At last, select E5.
After that, write the formula:

=MID(B5,25,2)

Further, press Enter to get the State name.

Use the Fill Handle tool in each column.
Finally, we get ‘Street, City, State, ZIP Code’ from the full address.

Download Practice Workbook

Download the following workbook to practice by yourself.

Separate Address.xlsx

Conclusion

Lastly, following the above procedures, you will be able to Separate the specific portion of a whole address in Excel. You can try the ‘try yourself’ section given in the practice sheet for your practice and let us know if you need help with more ways to separate the address in Excel. Feel free to provide comments, suggestions, or ask for any queries in the comment section below.

How to Separate Address in Excel (3 Effective Ways)