Add Zip Code to Address

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
I think in theory this might be possible but it really depends on how well-formed the data is.

That site has 3 required fields, but you only mention one column.

Perhaps if you posted some sample data we could help further.:)
 

cvasquez

New Member
Joined
Aug 23, 2009
Messages
21
The address is in one column like this:

123 30th ave, San Diego, ca
234 B St Ste #200, Del Mar, CA
etc

If I separate this column into three parts, would it be easier? Like this:
123 30th ave,--------San Diego,----------CA
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
It should be easier if all the data is like that.

Are all the parts of the address delimited by a comma?

I've just manually tried those but I had no luck I'm afraid, I'm certainly not an expert on US addresses and ZipCodes though.:)

I also tried one in a Google search but all that seemed to come up was links to grocery stores in the area.:eek:

Is that the sort of thing you are looking for, not the stores but the ZipCode for those areas/stores?
 

cvasquez

New Member
Joined
Aug 23, 2009
Messages
21

ADVERTISEMENT

Thanks for the reply.

I have 3 columns of data and would like to have the fourth one filled with the zip code.

The first column has the street address, the second column has the city, and the third has the state. I just need to fill in the fourth column.

Here is an actual address (and they are all in separate columns as I did text to column):

309 third ave Chula Vista, CA

I would like to fill the 3 fields required by http://zip4.usps.com/zip4/welcome.jsp then click submit and pull the zip code it gives into column 4. Is this possible?

Thanks
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
That address worked but I'm afraid at this time of night I'm going to leave this until tomorrow morning.

I might be taking the wrong approach but I was thinking of automating IE to do this.

But that would involve looking at the HTML code which will take a bit of time.:)
 

bethg7

Board Regular
Joined
Sep 9, 2009
Messages
77
You can download a CSV file with ALL zipcodes for all states including Puerto Rico and the Virgin Islands from the attached site. It has around 42000 lines; but you can delete any state that you don't need and all the APO's if needed to make it a smaller database. Then you can use VLookup to find the zips you need. It is absolutely free!

http://www.aggdata.com/free/zip-code

HTH
Beth
 

Watch MrExcel Video

Forum statistics

Threads
1,123,070
Messages
5,599,611
Members
414,325
Latest member
annamsreenu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top