# How to Separate the City State and Zip from the Address Using an Excel Formula – 4 Steps

The sample dataset contains Addresses in column B.Â  The Address includesÂ the Street, City, State, and Zip code.

You can use the LEFT, MID, RIGHT, SUBSTITUTE, and FIND functions to separate the Street, City, State, and Zip code from the Address.

### Step 1 – Combine the LEFT and the FIND Functions to Separate the Street from the Address

• Select C5.

• Enter the formula:
`=LEFT(B5, FIND(",",B5)-1)`

Formula Breakdown:

• In the FIND function “,” is the find_text, and B5 is the within_text of the FIND function.
• B5 is the text of the LEFT function, and FIND(“,”,B5)-1 is the num_chars of the LEFT function.

• Press Enter.

269 Market Drive is the output.

• AutoFill the rest of the cells in column C.

### Step 2 – Merge the MID, SUBSTITUTE, and FIND Functions to Separate the City from the Address

• Select D5, and enter the formula.
`=MID(SUBSTITUTE(B5,"Â  "," "), FIND(",",SUBSTITUTE(B5,"Â  "," "))+1,10)`

• Press Enter.

Morgantown is the output.

• AutoFill Â the rest of the cells in column D.

### Step 3 – Combine the LEFT and the RIGHT Functions to Separate the State from the Address

• Select E5, and enter the formula.
`=LEFT(RIGHT(B5,9),2)`

Formula Breakdown:

• In the RIGHT function, B5 is the text, and 9 is the num_chars of the RIGHT function.
• RIGHT(B5,9) is the text of the LEFT function, and 9 is the num_chars of the LEFT function.

• Press Enter.

TX is the output.

• AutoFill the rest of the cells in column E.

### Step 4 – Apply theÂ  RIGHT Function to Separate the Zip Code from the Address

• Select E5, and enter the formula.
`=RIGHT(B5,5)`
• B5 is the text of the RIGHT function and 5 is the num_chars of the RIGHT function.

• Press Enter.
• 75001 is the output.

• AutoFill the rest of the cells in column F.

Read More: How to Separate City and State in Excel

## Things to Remember

• Â When a value is not found in the referenced cell, the #N/A error is displayed.
• #DIV/0! error is displayed when a value is divided by zero(0) or the cell reference is blank.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

1. the formula to pull the City from an address does not pull the full name of the city, missing characters. The same for the formula for state, it only pulls in 1 of the 2 state letters (abbreviated state)

Osman Goni Ridwan Dec 10, 2023 at 12:22 PM

Hi Sue,
Thanks for your comment. In the formulas given in the article to retrieve the City name, we used SUBSTITUTE to remove extra spaces, find the position of the first comma, and extract the city name using the MID function. Also, we had to insert the length of the city name manually. If you want to make it dynamic, you can use the following formula in cell D5:
Dynamic Formula to Retrieve City Name from Full Address:
=MID(B5, FIND(“,”, B5) + 1, FIND(“,”, B5, FIND(“,”, B5) + 1) – FIND(“,”, B5) – 1)
This formula finds the position of the 1st and 2nd commas and extracts all characters between them to get the city name.

And for state names, we have used formula to retrieve only those in the abbreviated formats. If you want to get state names in full forms, then use the given formula:
Dynamic Formula to Retrieve Full State Name from Full Address:
=MID(B5, FIND(“,”, B5, FIND(“,”, B5) + 1) + 2, FIND(“,”, B5, FIND(“,”, B5, FIND(“,”, B5) + 1) + 1) – FIND(“,”, B5, FIND(“,”, B5) + 1) – 2)

Using this formula, you will get all characters between the 2nd and 3rd comma. Thus you will get the state name in full form.

Hope, your problem will be solved. If not, share with us in the reply or you can share your workbook in the ExcelDemy Forum.

Best Regards,
ExcelDemy Team

Advanced Excel Exercises with Solutions PDF