While working in Microsoft Excel with addresses, it might occasionally be beneficial to separate out the street address, city, state, and zip code or postal code. This may make the data simpler. It is simple to separate an address into a city, state, and zip code using Excel formulae. Additionally, this process saves time. In this article, we will demonstrate the procedures to separate address in Excel using formula.
Step-by-Step Procedures to Separate Address Using Formula in Excel
Assume that, we have the following dataset which contains some addresses. Now, we have given the right examples and provided sufficient instructions on how to efficiently separate the city, state, and zip code from the address using an Excel formula.
Step 1: Merge Excel LEFT & FIND Functions to Separate Street from Address
As we can see that the street address is the initiator of the address. We can separate the street name using the combination of the LEFT function and FIND functions. LEFT function returns the very first element or characters in a text string, based on the number of characters. The FIND function returns the location of one text string inside another. Let’s follow the sub procedures to use the functions to separate the street name.
- Firstly, we will get the street name. For this, select the cell where you want to put the combination of the formula of the LEFT and FIND functions. So, we select cell C5.
- Secondly, put the formula into that selected cell.
=LEFT(B5, FIND(",",B5)-1)- Thirdly, press Enter.
- Now, drag the Fill Handle down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the plus (+) symbol.
- Finally, you can see the street name is separated from the address.
🔎 How Does the Formula Work?
- FIND(“,”,B5)-1: The number of characters removed from the entire address using the FIND function. It does this by identifying the location of the “,” comma before deducting 1 from the result.
- LEFT(B5, FIND(“,”,B5)-1):Â This removes the specified characters from the address.
Read More: How to Separate Address in Excel
Step 2: Combine MID, SUBSTITUTE & FIND Functions to Separate City from Address
Now, we need to separate the city name. For this, we will use the combination of the MID, SUBSTITUTE, and FIND functions. The MID function returns a certain number of characters from a text string, beginning at the place you designate, Based on the number of characters we provide. The SUBSTITUTE function uses matching to replace text in a given string. If the search string occurs more than once, FIND provides the location of the first instance. Let’s see the sub-steps to use the combination of Excel functions to separate city from address.
- We will separate the city now. To begin with, choose the cell (D5) where you want to insert the MID, SUBSTITUTE, and FIND functions’ formula.
- Secondly, type the formula below into the selected cell.
=MID(SUBSTITUTE(B5,"Â "," "), FIND(",",SUBSTITUTE(B5,"Â "," "))+1,10)- Further, press the Enter key to finish the procedure.
- Furthermore, to copy the formula over the range, drag the Fill Handle down or double-click on the plus (+) icon.
- Lastly, you will be able to see the city name is separated from the address.
🔎 How Does the Formula Work?
- SUBSTITUTE(B5,” “,” “): It matches the replaced text or address.
- FIND(“,”,SUBSTITUTE(B5,” “,” “)): This provides the location of the first instance.
- MID(SUBSTITUTE(B5,” “,” “): This will start extracting the characters from a given position.
- MID(SUBSTITUTE(B5,” “,” “), FIND(“,”,SUBSTITUTE(B5,” “,” “))+1,10): This will extract the city name from the address.
Read More: How to Separate City and State without Commas in Excel
Step 3: Separate State from Address Using Excel LEFT & RIGHT Functions
In this step, we will extract the state from the address. We combine the LEFT and RIGHT functions for this. The RIGHT function returns the last character or characters in a text string, based on the number of characters we give. Let’s look at the instructions to use those functions for separating addresses.
- Similarly, as in the previous method, select cell E5 and substitute the formula to get the state from the address.
- Then, type the formula into the cell that we have selected.
=LEFT(RIGHT(B5,9),2)- Hit the Enter key to complete the process.
- The result will now display in the selected cell, along with the formula in the formula bar.
- After that, drag the Fill Handle to the bottom to reproduce the formula throughout the whole range. Double-click the plus (+) sign to AutoFill the range.
- Finally, by following the steps we can split the state from the address.
🔎 How Does the Formula Work?
- RIGHT(B5,9): It returns the last character of the text string or the address.
- LEFT(RIGHT(B5,9),2): This will extract the zip code from the address.
Read More: How to Separate City and State in Excel
Step 4: Use RIGHT Function to Separate Zip Code from Address in Excel
Now, finally, we just need to separate the zip code. For this, we are using the RIGHT function.
- Firstly, select the cell where you want to put the formula to a separate address. So, we select cell F5.
- Secondly, enter the below formula into that selected cell.
=RIGHT(B5,5)- Thirdly, press Enter to see the result in that selected cell.
- Now, you will be able to see the result in the selected cell and the formula will appear in the formula bar.
- Further, to copy the formula over the range, drag the Fill Handle down. Or, double click on the plus (+) sign to AutoFill the range.
- And, that’s it! The zip code is separate from the address.
Read More: How to Separate City State and Zip from Address Using Excel Formula
Things to Remember
- Sometimes users may be confused about where to separate the address, the street name is followed by a comma and has spaces both before and after it. Some of the street addresses also indicated by a comma. The city identity is followed by a semicolon and a space character. A two-letter code in capital letters with a space before and after the state. Nine or five numbers are the zip code. Also, a zip code could start with a zero.
- The MID, SUBSTITUTE, and FIND functions can be used to separate the city from an address. You must adjust the character length in this situation based on the address character.
- The #N/A error occurs in Excel even when a value cannot be found in the relevant cell.
- When the cell reference is blank, #DIV/0! error occurs.
Download Practice Workbook
You can download the workbook and practice with them.
Conclusion
The above procedures will assist you to Separate the Address using Formula in Excel. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback.
Related Articles
- How to Separate Address in Excel with Comma
- How to Separate Address Number from Street Name in Excel
- How to Split Inconsistent Address in Excel
<< Go Back to Address Format | Text Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
