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

Steps:

=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.

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

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.