69.4 F
San Francisco
Wednesday, September 16, 2020

Zipcode to City, State Excel Spreadsheet

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.


Zip-Codes-to-City-County-State-2020 (xlsx) – Updated April 2020

Zip Codes to City State 2019 Leading Zeros (xlsx) – Updated December 2019

Zip Codes to City State (xlsx) – Updated January 2019

Zip Codes to City State with Acceptable Cities (xlsx) – Updated January 2019

BETA Download

City-State-to-ZipCode-2020 (xlsx) – Updated May 2020

Edel Alonhttp://edelalon.com
Edel-Ryan Alon is a starving musician, failed artist, connoisseur of fine foods, aspiring entrepreneur, husband, father of two, geek by day, cook by night, and an all around great guy.

Related Articles

Halloween 2020 and COVID

Not sure how Halloween will work this year but it is good to know that companies like Hershey are putting out maps to show...

Working with Federal Office of Rural Health Policy (FORHP) Data Files

FORHP released a couple of excel files that contain information about FIPS and ZCTAs. For people with large amounts of data where they want...

20 Years On and Still Burning Bright – The Honda S2000

In the modern world, we are constantly pressed to have the latest things, and our cars are no exception. We’re told year after year...


  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.


  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!

  7. Hi Edel,

    I’m running into the same issue as John did. However, even though ran text to columns to remove the apostrophes and formatted the column as numbers, I’m still getting N/A for about 2/3rds of the file. Do you know of any other causes of this issue?

  8. Interesting, if I paste zip codes that start with zero in the top part of the spreadsheet where you formatted with the formula it works. If I copy and paste the formula to extend I get N/As. Can you extend your formatting to include more rows?

  9. Hi Christian,
    So far, the only cause has been that some programs/databases export and hide that apostrophe. Have you tried cutting, then pasting into notepad, and then copying from notepad back into the excel sheet?

  10. Hi Kathryn,
    Thanks for using the spreadsheet. The first document goes all the way to 200 rows. The second almost reaches excel’s end. Are you working with more than 200 rows? Thanks.

  11. Hi Kathryn,
    Thank you so much for catching that. I updated the files. Please download and test them. Let me know if it works.

    For everyone,
    Dollar Signs ($) matter in the real world and programming world too.

  12. You LITERALLY saved me so much time. I had 4,933 zip codes that needed a city. I am ETERNALLY grateful.

  13. Great tool! I am trying to add by region also. There was a programmer from Walmart who shared a similar program but adding cities provides more accurate shopping data and the addition of regions makes it all powerful.

  14. HI Edel, is there a file for the reverse? That will take a full street address without a zip code and convert and add in a zip code?

  15. Hi Edel Alon,

    Thanks for responding.

    I am not pretty sure but I thinks it is city population, as I am searching on google manually like this “Cantonment FL population” I am trying to find any excel code to find the population of the city using the city or zipcode but I am not able to find any on google

  16. Hi Edel Alon,

    Thanks a lot! I am currently trying this one and so far it works the best. I really appreciate this.


  17. Do you have an international postal code template? i have zips from around the world and need their country’s name

  18. This is so great Edel!!! One tip for those that go to copy in their list of zip codes and it returns an N/A, make sure you convert your data to an actual number. My zips were in text form, so I had to convert them to general and then “poof”, like magic, Edel saved me so much time and work 🙂

    One request would be for you to add population data for those zips. Very daunting task, but would be very useful!

  19. Hello,

    This is EXACTLY what I need but I don’t know how to use it past the 5 rows already set up. Can you help?

    I would like to integrate it into an existing spreadsheet.


  20. Hi Jeff,
    I think I understand the situation. The best thing to do is copy the cells above and paste them below. Let me know if that doesn’t do the trick.

  21. Hello Edel,

    I have a file with around 6,000 zip codes and when I paste it into your Excel sheet it is only returning about 1500 of the city and states. Not sure how to return all the city and states for the entire 6,000 zip codes.

  22. Amazing tool. Thank you so much. I normally google all my excel needs and yours was the most efficient and easy solution.

  23. Hi John,
    Thanks for reaching out. Are the ones missing all at the bottom or some lines are missing? If all at the bottom, you can copy the blank cells that work and paste them into the ones that do not work. I purposely did not extend the file past 1500 rows…or around there to keep the file size low. Or, after you paste in your zips, highlight one of the rows that works all the way to the bottom of the file. Then hit CTRL-D on your keyboard to Fill Down.
    Let me know these don’t work.

  24. Thank you so much, Edel! You don’t know how much this has helped me today. I had been working on a project all day only to lose everything. Your formulas saved me so much time and stress. Thank you, again!

  25. Hello Edel,
    Thank you. I really appreciate you taking the time to respond to my question and figuring out my issue – you’re solution works perfectly/extremely time saving and impressive!
    Thank you again,

  26. Question- is there a way we can bulk put in City, State, and then all zip codes would come out? Sort of the opposite here? If you built this out would be happy to venmo you!

  27. Hi there, I just tried the 2020 version and it didn’t work for me. I get #N/A in all the cells with IF/Vlookup formulas.

  28. Correction, the 2020 version works, but only if I manually type in the zip codes. If I paste them, I get #N/A.

    Any idea why?

  29. Hi Ray,
    We’ve noticed that the problem might be that the source (where you’re copying from) is formatted as “text”. The fix has been to (if copying within Excel), to highlight the original, format them as a number, then copy and paste into the sheet. Let me know if that does not work. Thanks.

  30. Thanks for the quick reply, Edel! I took your advice, formatted as Number, then pasted into your doc. Unfortunately, no cigar. I also tried formatting as General, and nope. Any other suggestions?

  31. For further context, if I format the source as number, then paste into the Zip Code doc, I get #N/A. If I click into a cell with a pasted Zip, and then press Enter, the formula then populates correctly. I’m working with thousands of zip codes, so want to avoid having to use that workaround, however.

  32. Yes, we’ve seen this in the past. The original source was putting in a ‘ before the number that could not be seen. If it’s not sensitive, you can email me the file so I can take a look at it.

    Email me file if you want me to take a look. 

  33. I have a data set from CT that simply will not past. I have tried converting the zip codes to number wiping out the front zeros but it still wont run ? ANy suggestion ? This looks like it could be a useful tool

  34. Hi Russell,
    Have you tried copying your data into notepad and then copying it from notepad to the excel file? That usually does the trick.


Please enter your comment!
Please enter your name here

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

Stay Connected

- Advertisement -

Latest Articles

Working with Federal Office of Rural Health Policy (FORHP) Data Files

FORHP released a couple of excel files that contain information about FIPS and ZCTAs. For people with large amounts of data where they want...

20 Years On and Still Burning Bright – The Honda S2000

In the modern world, we are constantly pressed to have the latest things, and our cars are no exception. We’re told year after year...

Lion Cake

Documenting a lion cake. Nothing too fancy, just a lion cake.

S2000 Glove Box Organizer

Just highlighting this glove box organizer for the S2000 made by Modify Products. They seem to have other items for sale that fit this...

Today’s Winner

Just finished a lobster feast at New England Lobster in Burlingame, California. Many of you read about the 15-pound lobster. It was not a...