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.


How to Separate Address in Excel: 3 Effective Ways

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


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


Download Practice Workbook

Download the following workbook to practice by yourself.


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.


Related Articles


<< Go Back to Address Format | Text Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Farzana Ferdous
Farzana Ferdous

Farzana Ferdous, a graduate of Materials & Metallurgical Engineering from Bangladesh University of Engineering & Technology, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, she exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Besides, she is also interested in Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo