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

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.

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.

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.

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.

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.

Practice Section

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

Download Practice Workbook

Finding Zip Code from Address.xlsx

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.

<< Go Back to Zip Code in Excel | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!