find zip code by address excel

Find Zip Code by Address in Excel

By
AD

Ever found yourself in need of extracting ZIP codes from a list of addresses in Excel, for tasks like targeted marketing, event planning, or data analysis? You're in luck, as this process is straightforward with the help of Excel's built-in tools and some simple steps. Let's dive into how you can easily find ZIP codes by address in Excel.

Before we start, ensure you have your address data in an Excel spreadsheet. For this process, the address should be in a single cell, typically containing the street number, street name, city, state, and ZIP code. Here's how you can extract the ZIP codes:

Using Excel's Built-in Text to Columns Feature

Excel's Text to Columns feature is a powerful tool that divides a single cell's content into multiple cells based on a chosen delimiter, which in this case is the comma. Let's explore how to use it:

AD
U.S. ZIP Codes: Free ZIP code map and zip code lookup
U.S. ZIP Codes: Free ZIP code map and zip code lookup
zip codes
zip codes
Split Address With Excel Formulas - Contextures Blog
Split Address With Excel Formulas - Contextures Blog

Step 1: Format Your Address Data

First, format your address data to ensure each address is in a single cell, separated by commas. For example, "123 Main St, Anywhere, AnyState, 12345".

Second, add a new column next to your address column. This new column will hold the extracted ZIP codes.

How to find the correct ZIP code or POSTAL code for your area
How to find the correct ZIP code or POSTAL code for your area
U.S. ZIP Codes: Free ZIP code map and zip code lookup
U.S. ZIP Codes: Free ZIP code map and zip code lookup
Whitepages
Whitepages

Step 2: Use Text to Columns

Select the range containing your address data and the new, empty column. Go to the Data tab in Excel's ribbon, then click on Text to Columns. Choose delimiters and set Destination Cell in the wizard, ensuring it points to the new, empty column you created.

Once done, click Finish, and you'll find the ZIP codes extracted into the new column.

AD

Using Excel's Remove النساء Feature

Alternatively, you can use the Remove Clap feature (also known as the SUBSTITUTE and LEN functions) to remove the text before and after the ZIP code. This method works well when your addresses are consistently formatted.

Excel - 30 Labels per Page (you supply addresses)
Excel - 30 Labels per Page (you supply addresses)
How to Make Address Labels Using an Excel Spreadsheet | Techwalla
How to Make Address Labels Using an Excel Spreadsheet | Techwalla
an image of a table with numbers and times on it in windows 7, 8 and 10
an image of a table with numbers and times on it in windows 7, 8 and 10
Keep Leading Zeros Fast in Excel
Keep Leading Zeros Fast in Excel
10012 and 10013 Are NYC's Most Prized Zip Codes | 6sqft
10012 and 10013 Are NYC's Most Prized Zip Codes | 6sqft
Zip Code Lookup API - Excel University
Zip Code Lookup API - Excel University

Assume your addresses are formatted like this: "123 Main St, Anywhere, AnyState, 12345". To extract the ZIP code (12345), follow these steps:

Step 1: Add a New Column for ZIP Codes

As before, add a new column next to your address column to hold the extracted ZIP codes.

AD

Step 2: Use the Remove Clap Formula

In the new column, enter the following formula: "=MID(A1,SEG(SUBSTITUTE(B1,",",REPT(" ",99)))-4,5)". Replace A1 and B1 with your respective address and new column cells.

Address Search | How To Find Someone's Address By Name
Address Search | How To Find Someone's Address By Name
find zip code by address excel
find zip code by address excel
an image of a computer screen with the name and number of items in each section
an image of a computer screen with the name and number of items in each section
a cell phone with the text excel tips on it and an image of a computer screen
a cell phone with the text excel tips on it and an image of a computer screen
Google Sheets: Find Unique Entries - Teacher Tech with Alice Keeler
Google Sheets: Find Unique Entries - Teacher Tech with Alice Keeler
an image of the list in google's web page with numbers and phone numbers
an image of the list in google's web page with numbers and phone numbers
I will do perfect data entry, web research and copy paste
I will do perfect data entry, web research and copy paste
What is a Zip Code | Definition of Zip Code
What is a Zip Code | Definition of Zip Code
a cell phone showing excel tips on the screen and an email list in the bottom right corner
a cell phone showing excel tips on the screen and an email list in the bottom right corner
Add Leading Zero
Add Leading Zero
Use this Excel spreadsheet format to import addresses | MyRouteOnline
Use this Excel spreadsheet format to import addresses | MyRouteOnline
an iphone screenshot shows the list of all different languages and numbers on the phone
an iphone screenshot shows the list of all different languages and numbers on the phone
20 Most-Asked Excel Job Interview Questions for Business Analyst
20 Most-Asked Excel Job Interview Questions for Business Analyst
Data Entry Form in Excel‼️ #excel
Data Entry Form in Excel‼️ #excel

The SEG(SUBSTITUTE(B1,",",REPT(" ",99)))-4 part of the formula finds the position of the last comma in the address, while the MID function extracts the last 5 characters (the ZIP code) from this position.

Press Enter, and you should see the ZIP code extracted into the new column. Drag the formula across to apply it to all addresses.

AD

Using either of these methods, you can now easily find ZIP codes by address in Excel, streamlining your data analysis and planning processes.

Keep experimenting with Excel's features to simplify other complex tasks. Happy Excel-ing!

AD