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

Our dataset includes the Addresses of ABC Company employees. Our goal is to find the ZIP Codes from the given addresses.

excel find zip code from address


Method 1 – Applying Excel Functions

Steps:

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

  • 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


Method 2 – Utilizing CDXZipStream Add-in

Steps:

  • Right-click on the cell you want to display the Zip Code. In this case, we used cell C5.
  • Select the CDXZipStream Functions option.
  • Choose the Insert CDXZipList Function.

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

  • In the dialogue box, choose the State, County, and City from the marked fields.
  • Click OK.

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

We have provided a Practice Section on the right side of the Excel Workbook.

Practice section to find ZIP Code from the address in Excel


Download the Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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