Zipcode to City, State Excel Spreadsheet

6567

 

I just decided to do this. I’ve been meaning to learn more about the vlookup function in Excel. I figured this one would be a semi-useful template for you to use. This is best used on Excel 2010.

The way to use the sheet is just to copy and paste all of the zipcodes in the first column then the rest is magic.

Download
Zip Codes to City State (xlsx)

Zip Codes to City State with Acceptable Cities (xlsx)

 

18 COMMENTS

  1. Excellent work! Just was asked by my boss to list employees by state, and my list was all zip codes! Worked like a charm!

    Oh, zips which begin with zeros are not in the vlookup table, Connecticut, Massachusetts, Maine, New Hampshire, New Jersey, Rhode Island, and Vermont.

    Thanks!

  2. Hi Edel,

    Question for you. Some zip codes have multiple (“acceptable cities”), but the vlookup stops at the first one. I love the granularity you offer, but it doesn’t pull those zip codes … often it’ll just pull the big city (i.e. Saint Louis). How can I modify it so that it pulls all “acceptable cities” for that zip code?

    Thanks for your awesome work.

  3. Is there some reason when I copy my list it returns #n/a error but if i then format cell and retype the zip code the vlookup works?

  4. The copy paste function returns #n/a error for me. If I copy format and then retype the zip code in the vlookup works. What causes this? Defeats the copy and past of my zip code list. Assume there is something silly going on with me.

  5. Was there a reply on that last comment? I am having the same issue. When I copy and past most say n/a. When I type the zip code in manually it works. Is there a fix to this?

  6. Hi Mark,
    Worked a while on John’s file. The problem we encountered is that the cells with the zipcodes have a hidden apostrophe. It looks like the apostrophes came in during an export from another system. What you might be able to do is to select the column, right click on the data and select Format Cells. Under the Number tab, make sure Number is selected. It might be text. Click OK and then try to copy/paste it again.

    Another option if you’re dealing with a big chunk of data is to try to remove the apostrophe by doing a text-to-column. This fastest solution I found so far: highlight the column, go to data, then text-to-columns, select delimited, then make one of the choices to be an apostrophe.

    Let me know if that works. I think there’s also an option to paste as value but we found that it still carried over the apostrophe. Keep us posted!

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.