We have the following dataset which contains some addresses. We will separate the address into parts separated by comma.
Step 1 – Merge Excel LEFT and FIND Functions to Separate the Street from Address
- Select the cell where you want to get the street name. We selected cell C5.
- Insert the following formula:
=LEFT(B5, FIND(",",B5)-1)- Press Enter.
- Drag the Fill Handle down to duplicate the formula over the range or double-click on the plus (+) symbol.
- Here’s the first value.
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 the City
- Insert the following formula in D5:
=MID(SUBSTITUTE(B5," "," "), FIND(",",SUBSTITUTE(B5," "," "))+1,10)- Hit Enter.
- Drag the Fill Handle down or double-click on the plus (+) icon.
- Here’s the second value extracted.
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 the State with the LEFT and RIGHT Functions
- Select cell E5 and enter this formula to get the state from the address.
=LEFT(RIGHT(B5,9),2)- Hit the Enter key.
- Drag the Fill Handle to the bottom to reproduce the formula throughout the whole range.
- Here’s the next value.
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 the RIGHT Function to Separate the Zip Code from the Address
- Enter the following formula into E5:
=RIGHT(B5,5)- Press Enter.
- Drag the Fill Handle down or double-click on the plus (+) sign to AutoFill the range.
- Here’s the result.
Read More: How to Separate City State and Zip from Address Using Excel Formula
Things to Remember
- The #N/A error occurs in Excel even when a value cannot be found in the relevant cell.
- When the cell reference is blank, the #DIV/0! error occurs.
Download the Practice Workbook
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!
