Autofill a city and state from a zipcode

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Drewscifer - 19 Oct 2007 15:46 GMT

I have about 400 addresses to enter in. I was wondering if there was a way,
in excel for me to type in a zip code and have Excel fill in the city and
state automatically.

Reply to this Message

Peo Sjoblom - 19 Oct 2007 15:59 GMT

You need to create a table first, then you can use a VLOOKUP formula, ample
description on how to use that formula is in help. Create the table like

zip city state

then use 2 vlookups, one for the city and one for the state

Signature

Regards,

Peo Sjoblom

>I have about 400 addresses to enter in. I was wondering if there was a way,
> in excel for me to type in a zip code and have Excel fill in the city and
> state automatically.

Reply to this Message

Drewscifer - 19 Oct 2007 16:26 GMT

So Excell does not contain or cannot access all US zipcodes?

> You need to create a table first, then you can use a VLOOKUP formula, ample
> description on how to use that formula is in help. Create the table like
[quoted text clipped - 6 lines]
> > in excel for me to type in a zip code and have Excel fill in the city and
> > state automatically.

Reply to this Message

JP - 19 Oct 2007 17:24 GMT

Hello,

You would have to buy that from USPS or one of the many vendors that
sells this information.

HTH,
JP

On Oct 19, 11:26 am, Drewscifer <Drewsci...@discussions.microsoft.com>
wrote:

> So Excell does not contain or cannot access all US zipcodes?
>
[quoted text clipped - 6 lines]
>
> > --

Reply to this Message

JE McGimpsey - 19 Oct 2007 17:28 GMT

No. It's a spreadsheet application...

> So Excell does not contain or cannot access all US zipcodes?

Reply to this Message

CLR - 19 Oct 2007 16:03 GMT

The VLOOKUP formula will perform the task you wish. You will first need to
set up a table of all the possible ZipCodes/Cities/States you are interested
in.

Vaya con Dios,
Chuck, CABGx3

> I have about 400 addresses to enter in. I was wondering if there was a way,
> in excel for me to type in a zip code and have Excel fill in the city and
> state automatically.

Reply to this Message

JE McGimpsey - 19 Oct 2007 16:03 GMT

If you have a table of zipcodes, e.g., in a separate sheet (say, sheet
"Zips"):

A B C
1 Zip Code City State
2 00000 City1 ST
3 00001 City2 ST
...

You could then use, in your entry sheet:

A1: <zip code>
B1: =VLOOKUP(A1,'Zips'!A:C,2,FALSE)
C1: =VLOOKUP(A1,'Zips'!A:C,3,FALSE)

> I have about 400 addresses to enter in. I was wondering if there was a way,
> in excel for me to type in a zip code and have Excel fill in the city and
> state automatically.

Reply to this Message

aorick@gmail.com - 19 Nov 2007 05:18 GMT

You can download a zip code database here:
http://databases.about.com/od/access/a/zipcodedatabase.htm

Reply to this Message