How to Separate Address in Excel (3 Effective Ways)

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.


Download Practice Workbook

Download the following workbook to practice by yourself.


3 Effective Ways to Separate Address in Excel

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.

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

STEPS:

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

Use the ‘Text to Columns’ Option to Split Address in Excel

  • 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.

Use the ‘Text to Columns’ Option to Split Address in Excel

  • 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.

Use the ‘Text to Columns’ Option to Split Address in Excel

  • 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:

Read More: How to Split Inconsistent Address in Excel (2 Effective Ways)


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.

Separate Address into Different Columns with Flash Fill Feature

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

Separate Address into Different Columns with Flash Fill Feature

  • 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.

Separate Address into Different Columns with Flash Fill Feature

  • 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:

Read More: How to Separate Address in Excel Using Formula (With Easy Steps)


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.

Apply Excel LEFT, RIGHT and MID Functions to Separate Address

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.

Apply Excel LEFT, RIGHT and MID Functions to Separate Address

  • Thirdly, select cell D5.
  • Then, write the formula below:
=MID(B5,18,5)
  • Press Enter to get City Name.

Apply Excel LEFT, RIGHT and MID Functions to Separate Address

  • At last, select E5.
  • After that, write the formula:
=MID(B5,25,2)
  • Further, press Enter to get the State name.

Apply Excel LEFT, RIGHT and MID Functions to Separate Address

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

Read More: How to Separate City State and Zip from Address Using Excel Formula


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. For more Articles with related topics please keep your eye on the ExcelDemy website. Feel free to provide comments, suggestions, or ask for any queries in the comment section below.


Related Articles

Farzana
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo