How to Separate City State and Zip from Address Using Excel Formula

Get FREE Advanced Excel Exercises with Solutions!

While working with Microsoft Excel, sometimes, we have to separate an address into a street, city, state, and zip code. Separating an address into a city, state, and zip code using Excel formulas is an easy task. This is a time-saving task also. Today, in this article, we’ll learn four quick and suitable steps in how to separate city, state, and zip code from address using Excel formula effectively with appropriate illustrations.


How to Separate City State and Zip from Address Using Excel Formula: 4 Easy Steps

Let’s assume we have an Excel worksheet that contains the information about several Addresses in column B.  The Address contains the Street, City, State, and Zip code. We will use the LEFT, MID, RIGHT, SUBSTITUTE, and FIND functions in Excel so that we can easily separate the Street, City, State, and Zip codes from the Address. Here’s an overview of the dataset for today’s task.

excel formula to separate address city state and zip


Step 1: Combine LEFT and FIND Functions to Separate Street from Address

In this step, we will apply the LEFT and FIND functions to separate an address from a street, city, state, and zip code. This is an easy task. Let’s follow the instructions below to separate an address from a street, city, state, and zip code!

  • First of all, select cell C5.

  • After selecting cell C5, type the below formula in that cell. The function is,
=LEFT(B5, FIND(",",B5)-1)

Formula Breakdown:

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

excel formula to separate address city state and zip

  • After typing the formula in Formula Bar, simply press Enter on your keyboard. As a result, you will get 269 Market Drive as the output of the functions.

excel formula to separate address city state and zip

  • Hence, AutoFill the LEFT and FIND functions to the rest of the cells in column C.

excel formula to separate address city state and zip

Read More: How to Separate Address in Excel with Comma


Step 2: Merge MID, SUBSTITUTE, and FIND Functions to Separate City from Address

Now, we will apply the MID, SUBSTITUTE, and FIND functions to separate the city from the address. Let’s follow the instructions below to separate the city from the address!

  • First, select cell D5, and write down the MID, SUBSTITUTE, and FIND functions to separate the city from the address of that cell.
=MID(SUBSTITUTE(B5,"  "," "), FIND(",",SUBSTITUTE(B5,"  "," "))+1,10)

  • After that, simply press Enter on your keyboard and you will get Morgantown as the output of the MID, SUBSTITUTE, and FIND functions.

excel formula to separate address city state and zip

  • Hence, AutoFill the MID, SUBSTITUTE, and FIND functions to the rest of the cells in column D.

Read More: How to Separate Address Number from Street Name in Excel


Step 3: Combine LEFT and RIGHT Functions to Separate State from Address

In this portion, we will merge the LEFT and the RIGHT functions to separate the state name from the address. This is an easy task. From our dataset, we will separate the state name from the address. Let’s follow the instructions below to separate the state from the address!

  • First, select cell E5, and write down the LEFT and the RIGHT functions of that cell.
=LEFT(RIGHT(B5,9),2)

Formula Breakdown:

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

excel formula to separate address city state and zip

  • Further, simply press Enter on your keyboard and you will get TX as the output of the LEFT and the RIGHT functions.

excel formula to separate address city state and zip

  • Hence, autoFill the LEFT and the RIGHT functions to the rest of the cells in column E.

Read More: How to Separate Address in Excel


Step 4: Apply RIGHT Function to Separate Zip Code from Address

Last but not the least, we will apply the RIGHT function to separate the zip code from the address. We can easily separate the zip code from the address from our dataset using the RIGHT function. Let’s follow the instructions below to separate the zip code from the address!

  • First, select cell F5, and write down the RIGHT function of that cell.
=RIGHT(B5,5)
  • Where B5 is the text of the RIGHT function and 5 is the num_chars of the RIGHT function.

excel formula to separate address city state and zip

  • Hence, simply press Enter on your keyboard. As a result, you will get 75001 as the output of the RIGHT function.

  • Further, AutoFill the RIGHT function to the rest of the cells in column F which has been given in the below screenshot.

excel formula to separate address city state and zip

Read More: How to Separate City and State in Excel


Things to Remember

👉 While separating the city from an address, you can use the MID, SUBSTITUTE, and FIND functions. In this case, you have to change the character’s length basis on the address character.

👉 While a value can not found in the referenced cell, the #N/A error happens in Excel.

👉 #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

I hope all of the suitable methods mentioned above to separate the street, city, state, and zip code from an address will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


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.
Md. Abdur Rahim Rasel
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

2 Comments
  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)

    • Reply Avatar photo
      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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo