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

Get FREE Advanced Excel Exercises with Solutions!

Finding the 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 the ZIP Code from the address in Excel. In this article, we will learn 2 convenient methods to find ZIP codes in Excel. So, let’s start this article and explore these methods.


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

In this section of the article, we will learn 2 convenient methods to find ZIP codes from addresses 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: [Fixed] Zip Codes in Excel Starting with 0


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 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 Sort Data by Zip Code in Excel


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


Download Practice Workbook


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.


Related Articles


<< Go Back to Zip Code in Excel | Number Format | 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.
Zahid Hasan
Zahid Hasan

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo