Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Find ZIP Code from Address in Excel (2 Easy Ways)

Finding ZIP Code or Postal Code can be a troublesome job to do in Excel if you don’t know the correct procedure. But it’s rather quite an easy thing to do. By following some simple steps, we can find ZIP Code from the address in Excel. In this article, we will learn 2 convenient methods to find ZIP Code in Excel. So, let’s start this article and explore these methods.


Download Practice Workbook


2 Convenient Ways to Find ZIP Code from Address in Excel

In this section of the article, we will learn 2 convenient methods to find ZIP Code from address in Excel. Let’s say, we have the Addresses of Employees of ABC Company as our dataset. Our goal is to find the ZIP Codes from the given addresses.

excel find zip code from address

Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.


1. Applying Excel Functions

Applying Excel functions can be a smart way to find the ZIP Code from an address if it is available in Excel cells. In the given dataset, we have the ZIP Code inside the Address. In that case, we can use the combination of the RIGHT and the SUBSTITUTE functions of Excel. Now, we will find these ZIP Codes using the above-mentioned functions of Excel. Let’s use the steps outlined in the following section to do this.

Steps:

  • Firstly, use the following formula in cell C5.
=RIGHT(SUBSTITUTE(B5,", USA",""),5)

Here, cell B5 indicates the first cell of the Address column.

Formula Breakdown

  • SUBSTITUTE(B5,”, USA”,””) → It substitutes “, USA” of the text string of cell B5 by a blank (“”) .
    • Here, B5 → It is the text argument.
    • “, USA” → This indicates the old_text argument.
    • “” → It represents the new_text argument.
    • Output Drivetrain Fitness, 4204 Tennyson St, Denver, CO, 80212.
  • Now, the RIGHT function will return the last 5 characters from the output of the SUBSTITUTE function.
    • Output 80212.
  • Following that, press ENTER.

Applying Excel Functions to find ZIP Code from the address in Excel

As a result, you will have the ZIP Code of the first Address in cell C5.

  • Finally, apply the AutoFill feature of Excel to get the remaining ZIP Codes as demonstrated in the following picture.

Final output of method 1 to find ZIP Code from the address in Excel

Read More: How to Lookup ZIP Code in Excel (4 Suitable Methods)


Similar Readings


2. Utilizing CDXZipStream Add-in

In contrast to the first method, you may need to find the ZIP code from an address where it is not present. Utilizing the CDXZipStream add-in is another efficient way to find it. The CDXZipStream add-in is not available in Excel by default. You can download the trial version of the add-in from the website of CDXZipStream and install it on your computer. One thing to keep in mind is that before installing the add-in, you need to close the Excel application if it’s running on your computer. After a successful installation of the add-in, you will see that the CDXZipStream tab is added to the Ribbon as shown in the following image.

Utilizing CDXZipStream Add-In  to find ZIP Code from the address in Excel

Now, let’s follow the procedure outlined below to do this.

Steps:

  • Firstly, right-click on the cell you want to display the Zip Code. In this case, we used cell C5.
  • After that, select the CDXZipStream Functions option.
  • Then, choose the Insert CDXZipList Function.

As a result, the Create Zip Code List dialogue box will open on your worksheet.

  • Now, in the dialogue box, choose the State, County, and City from the marked fields respectively.
  • Finally, click OK.

Subsequently, you will have the ZIP Code for your selected City, County, and State in cell C5.

  • Follow the same procedure for the rest of the Addresses and you will get the following outputs as shown in the image below.

Final output of method 2 to find ZIP Code from the address in Excel

Read More: How to Create Zip Code with Excel Formula (6 Easy Ways)


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

Practice section to find ZIP Code from the address in Excel


Conclusion

So, these are the most common & effective methods you can use anytime while working with your Excel datasheet to find ZIP Code from address in Excel. If you have any questions, suggestions, or feedback related to this article you can comment below. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy.


Related Articles

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo