Farzana Ferdous
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.
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:
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.
- 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:
Read More: How to Separate Address in Excel Using Formula (With Easy Steps)
Similar Readings
- How to Make an Address Book in Excel (An Ultimate Guide)
- How to Separate Address Number from Street Name in Excel (6 Ways)
- Create Email Address with First Initial and Last Name Using Excel Formula
- How to Separate Address in Excel with Comma (3 Easy Methods)
- Formula to Create Email Address in Excel (2 Suitable Examples)
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.
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
- How to Organize Addresses in Excel (2 Effective Ways)
- Format a Column for Email Addresses in Excel (2 Easy Ways)
- How to Separate City and State without Commas in Excel (3 Smart Ways)
- Format Address Labels in Excel (3 Steps)
- How to Make Address Labels in Word from Excel (With Easy Steps)
- How to Separate City and State in Excel (3 Effective Methods)
