Our dataset includes the Addresses of ABC Company employees. Our goal is to find the ZIP Codes from the given addresses.
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.
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.
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.
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.
Download the Practice Workbook
Related Articles
- How to Format Zip Codes in Excel
- How to Format Zip Code to 5 Digits in Excel
- How to Remove Last 4 Digits of Zip Code in Excel
- How to Concatenate ZIP Codes in Excel
<< Go Back to Zip Code in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
