How to break or split address into separated parts in Excel?

When dealing with addresses, it is sometimes helpful to have the individual components of the address broken out into their respective categories, such as the street address, the city, the state, and the zip code.

You may find it simpler to filter, sort, and query the data in this way, as well as use it for a variety of other purposes. It would be helpful if you had one column that contained simply the state portion of the individual’s addresses in the event that you wish to sort people based on the state in which they are located.

This post guides you through the steps necessary to break or split multiple addresses into their component parts using Excel.

Break or Split Address Into Separated Parts in Excel

Let’s understand step by step with an example.

Step 1

In the first step, we have a sample addresses which are separated using comma as shown in the below screenshot.

You may have noticed that each of the addresses in the screenshot that was just displayed has a comma that separates the various components of the address.

Because of this, we can now use the Text-to-columns feature to divide the various components of the address into distinct columns by employing the comma delimiter. This is a pretty helpful feature because it saves us a lot of time.

This can be accomplished by following these steps.

Step 2

The following titles should be placed at the top of each of the new columns that will be used to store the various components of the addresses.

Step 3

Choose the cells that contain addresses that you want to split up into sections, then go to the Data tab from the menu and select Text to Columns under Data Tool group. Please check out the below screenshot for the same.

Step 4

After that, Convert Text to Columns Wizard- Step 1 of 3 dialog boxes appeared. Now, Select the Delimited option under the original data type, and then click the Next button after making your selection. Please refer to the below screenshot for the same.

Step 5

In the Convert Text to Columns Wizard- Step 2 of 3, under the Delimiters group, make sure that the checkbox that is located next to Comma is checked. To proceed, select the Next button. Please check out the below screenshot for the same.

Step 6

The last step, select a cell for locating the separated address portions in the Destination box, and finally click the Finish button to close the dialog box. Please refer to the below screenshot for the same.

Step 7

It is clear that the chosen addresses are broken up into their individual components. Check out the below screenshot for the same.

Conclusion

In this tutorial, we used a simple example to demonstrate how you can break or split address into separated part using Text-To-Columns.