Zipcode to City, State Excel Spreadsheet

1. So helpful, thank you!

2. This is amazing! Thank you!

3. 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!

4. correction!!! zips with zeros do work! I had them all in my spreadsheet like this—> ‘06010

5. This is so awesome. Thanks for your work

6. This is awesome thanks

7. Thanks! Let me know if you need any modifications to it.

8. 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.

9. Hi D,
   Thank you for the comment. I added a new one to add the alternate cities in the list. Let me know if you have any electronic drum set recommendations. I need to get rid of my rig. Thanks.

10. This is a life saver!

11. Thanks Chad! Glad it worked out.

12. 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?

13. 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.

14. Hi John,
    I’m trying a couple of situations but I can’t replicate it. Can you send me your original list?

15. I sent you the file to your email address. Any luck?

16. 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?

17. You. Are. The. Best. Thank you!

18. 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!

19. Amazing! Thank you so much!
    Any plans on making one with area codes?

20. Nope but that’s a great idea! Will add to the task lists.

21. 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?

22. 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?

23. 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?

24. 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.

25. Yes – and the zipcodes are on the second tab but show NA on the first tab.

26. 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.

27. Hi
    No idea what I am doing wrong but I copy and paste and nothing happens. Is there a trick? Thanks PG

28. Hi PG,
    Thanks for using the tool. When you cut and paste, do you see anything like this video? https://youtu.be/rq7AFo9a4yQ

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

30. Awesome! Glad it helped!

31. Hi Edel, How impossible would it be to add “county” column?

    Great tool!

32. Hi Tyler,
    Not impossible…I’m looking for the data set now. That’s the difficult part.

33. 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.

34. Wow!
    Huge time saver!
    tks for your help.

35. THANK YOU!!

36. 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?

37. Hi Andrew, sorry, I don’t have that one. Wait, do you have City/State too?

38. Really appreciate your work. Thank you.

39. Thank you so much!! Enormous help.

40. This is very helpful for me, do you also have a way to do it with population?

41. Census block or city population?

42. 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

43. Ahhh…do you have a certain state? I might be able to make something for you that returns something like this: https://www.california-demographics.com/zip_codes_by_population

44. for now Florida and Texas is what I am trying to figure out.

45. Hi jodenzle,
    Give this one a shot: https://edelalon.com/blog/2019/05/zipcode-to-city-state-and-population-excel-spreadsheet/ Let me know if it works/doesn’t work for you. Unfortunately, I cannot promise how accurate the data is. I grabbed the numbers from https://www.louisiana-demographics.com/zip_codes_by_population and https://www.texas-demographics.com/zip_codes_by_population.

    Hope this works out for you.

46. Thank you! Amazing time saver for a school survey project. Real Deal!

47. Hi Edel Alon,

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

    Regards,
    Joden

48. This is great. Thanks so much for making this.

49. What is the source for the zip codes?

50. Hi Gerry,
    I forgot exactly but I’m pretty sure it was from this site: https://catalog.data.gov/dataset/zip-codes-zipcodes

    If you have another dataset, I can make the workbook for you.

51. Can this do the same thing but the other way round, I have city and state, but have to look all the zip codes up seperately.

52. Hi James, it does not do the same. Let me try to reverse engineer it.

53. Hi James,
    What does your data look like? Each city and state in their own column?

    CITY | STATE

54. You rock brother. I’d appreciate anything you could do for me.

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

56. Any chance to get this to include County?

    Thanks!

57. Nevermind I figured it out! Just change the very last number in the code to “7”

58. wow thank you for this

59. Is this form working, I pasted my zip code now what?
    Am i supposed to erase the other data?

60. Hi Keith,
    After pasting your zip codes in the first column, you should see the changes on the right. What do you see now?

61. 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!

62. Thank you Edel, this worked great and you saved me a ton of time

63. Thanks Candice,
    That’s in the works…after we get the census results.

64. You’re welcome!

65. 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.

    THANK YOU

66. 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.

67. I don’t generally leave comments, but this is the most amazing thing I never knew I needed.

    Awesome!

68. Glad it worked out for you.

69. 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.

70. Also, all the zips that start with a zero are not returning values. Zips with zeros return #NA.

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

72. 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.

73. Ah, I see what’s going on. The zipcodes that start with zero are pasting in as text, not a number. I would follow the information on this page to make sure the cells are formatted to have a leading zero as a number not text. https://www.ablebits.com/office-addins-blog/2017/01/18/add-leading-zeros-excel/ And then on my sheet, do the same. I’ll post up a new version that helps retain that leading zero.

74. I modified the file you sent me and sent it back. I saw some that did not return an area code but at least added the city.

75. OMG, Edel. This is a gift! You saved me two hours of work. Thank you!

76. You’re welcome! Happy new year!

77. Thank you! This saved me so much time!

78. Glad it worked out for you Frankie!

79. Outstanding!

80. 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!

81. Appreciate your work! TY

82. Cool. Glad it worked out for you!

83. Edel – great tool. I have the same question as a few above – would you be able to include a column for Counties? I have been trying unsuccessfully using the excel forum: https://www.excelforum.com/excel-formulas-and-functions/1187132-how-to-auto-fill-counties-by-zipcode-in-excel.html
    This apparently contains the zip file for counties but I am far from an excel wizard. Thank you.

84. Hi Ethan,
    I’ll take a look. I might need to pull another data set. Will keep you posted.

85. Hi Ethan,
    I did not get a copy from the forum (I didn’t bother to create an account to login). I did pull some county information. Please take a look at this sheet to see if it works for you. https://edelalon.com/blog/wp-content/uploads/2013/09/Zip-Codes-to-City-County-State-2020.xlsx

    Let me know if this is what you were looking for. Thanks for reaching out, I’ve added this to my list of downloads.

86. 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,
    Ethan

87. You’re welcome. I’m glad it worked out for you. Have a great time sheltering in place.

88. Thank you so so so much for this! Life saver!

89. 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!

90. Hmmm…I think that’s possible. Please hold.

91. Hi Megan,
    I have something but I’m not happy with it. The problem is that many cities have multiple zip codes. This sheet will return the first zip code that matches. Here’s a link: https://edelalon.com/blog/wp-content/uploads/2020/05/City-State-to-ZipCode-2020.xlsx

    I’m working on another solution.

92. Hi Megan,
    Here’s a solution that will return all the zip code of a city you type. But you have to type the exact city and state: http://edelalon.com/zip

93. 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.

94. 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?

95. 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.

96. 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?

97. 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.

98. 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. 

99. Also…did you try to copy and then paste values? https://www.youtube.com/watch?v=rq7AFo9a4yQ

100. 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

101. 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.

102. how do i make this work in google sheets??

103. Hi Tammi,
     I have not built one for google sheets. I’ll take a look into it.

104. Would you be able to add a feature that would convert the zipcode to rural (yes vs. no) using this FORHP Eligible ZIP Codes https://www.hrsa.gov/rural-health/about-us/definition/datafiles.html

105. Hi Elizabeth,
     Are you looking for something where you put in a zipcode and then it returns: State, County, FIPS, and CBSADesig?

106. The FORHP Eligible ZIP Codes only has state. What’s the final result you’re looking for?

107. Hello,

     Can this be reversed? Meaning enter the city /state and have a zip returned?

108. Will this work for you? https://edelalon.com/blog/2020/09/working-with-federal-office-of-rural-health-policy-forhp-data-files/

109. Hi Justin,
     It might be possible. I was thinking city but there are too many cities with the same name. Let me see what I can do.

110. Justin,
     Do you think a column with city and a column with state? Or were you thinking more like “city, state”?

111. Thank you so much for this. This helped me tremendously. Please don’t delete this blog as I am sure it will help more people in the future!

112. Edel,

     This is AMAZING! So kind of you to share.

     Does this work with Canadian addresses?

113. Thanks Ray, It does not work with Canadian addresses. If you can find me a good data set, I can make one.

114. You are a god-send!!! I was able to grab a file, where should I send?

115. Hi Ray, I emailed you some info.

116. Hey that was really helpful. Thanks a bunch for just providing that free without making people jump through a ton of hoops. 🙂

117. You’re welcome. Glad it’s around and being used.

118. You are a good person

119. Thanks Chris! Glad it worked out for you.

120. This is amazing. been looking for something like this!! my data stops showing up after ~530 zips. is there a hard stop? Or is this a human error? i tried looking thru comments to see if anyone else had this problem – sorry if i missed the solution!

121. Hi Margaret,
     Copy 1 row that has data and paste it in the missing cells below. I didn’t populate all the way to the bottom because it would have made the file really big.

     Let me know if this doesn’t work. Thanks.

122. Edel, man this is awesome man! Just used the April 2020 version and it works great…minus that you have to only use 5 digit zips which is still great. I did run into one issue that I was hoping you could possibly help me wit the workaround.

     I got the zips to all populate the city and other data, however when I try to copy the city data to another spreadsheet it carries over the formula and not the result (name of city)…is there something that I’m doing wrong or could do to copy just the text names without needing the formula?

     Thank you sir!

123. Hi Travis,
     Thank you. In the file, copy the cells you want. In the new spreadsheet, click on Home, then Paste. It will give you a few options. Choose Values, which is the 123 on the clipboard. Let me know if this doesn’t work. Thanks again for viewing.

124. Hello Edel, do you happen to have source or citation that explains how you compile this data? I ask because I want to make sure that the output of city, state and county for a zipcode is accurate, without having to manually check every single zipcode I have in my data sheet. I also want to be able to accurately cite you for how I transformed our zipcodes into city, state and county. Thank you for your time and this amazing spreadsheet.

125. Hi Anthony,
     I forgot exactly but I’m pretty sure it was from this site: https://catalog.data.gov/dataset/zip-codes-zipcodes

     Thanks for viewing and commenting.

126. Wow, you came to the rescue for me. I hate Excel and needed to quickly dump out state data based on a bunch of zip codes I had. This worked beautifully!

     FYI, I love your car! I’m still rocking my 2010 original Mazda Speed 3 with turbo (275 HP). Mazda almost hit the mark with the 2021 model but fell short, in my opinion. I’ve always had my eyes on the MX5.

127. You’re welcome! Glad it was useful! Yes, the MX5 rocks! I’m thinking of throwing in a turbo at some point.

128. Hi Edel,
     I can’t find the true source of the data. How are you able to update it with 2020 data?
     Also, can you confirm if city column are true cities or can it represent a town?

129. Hi Cindy,
     Looks like they took it down. I can redo with current data if you can help me find a good source. The city column is part of the original data set, they called it “city”. Let me know what you find. Thanks.

130. Thanks Edel for responding so quickly! It looks like the city field is a combination of cities and towns or incorporated places. I guess it really depends on ones definition of a “city”. I found a suitable dataset in the following link : https://www.census.gov/data/datasets/time-series/demo/popest/2010s-total-cities-and-towns.html . It’s called “Subcounty Resident Population Estimates: April 1, 2010 to July 1, 2019 (SUB-EST2019)”, which indicates the name of the place, whether if it’s city or town, and state in case need anyone needs to do further analysis. One can compare “Accident, MD” across your file and this.

     Overall, pretty much every other data source I’ve found with “city” as a column has a mix of actual cities and towns in the field. Thanks for putting this together! Hopefully we can find a new source to update this version with the new census info coming out.

131. Do you have a mapping file from zip code or address to Wind Tiers (Texas to Maine coastal regions)

132. Hi SM,
     Sorry, I do not have that information. It’s on my list of things to do. I do know that ESRI released a lot of free maps but I haven’t taken a look to see if it maps to zips. It could just be voting districts.

133. Great work – thanks for putting this together and making it available for download

134. You’re welcome!

135. So, so helpful! Thank you for making this free for anyone to use!

136. Thank you so much for making this! I have a question, I’m trying to paste a large number of zips, but for some reason, data will only populate in the columns up until line 537. Any idea how to get around this? I can paste my zips in chunks of 500 to get the info I need, but it would be great if I could paste all of them at once. Thanks!

137. Hi John,
     You’ll need to copy the cells on the right side and paste them into the blank cells down…as far as you want. I limited it because if I didn’t, the file size would be too large. Let me know if that doesn’t work for you. Thanks.

138. Thanks for the article! Good stuff

139. You’re welcome! The formulas used definitely help me out with many other things in Excel.

140. Edel, thanks for the database.
     Was wondering if it is possible to type an address and then get the zip code, city, etc.?

     Thanks man

141. Hi Seb,
     Unfortunately, not at this time. I’ve been trying to figure that one out too. There are just too many ‘100 Main Street’ addresses out there. Thanks for visiting!

142. Thank you for putting this together and more importantly for making it available to the public. I had the impossible task of trying to determine the states from +40,000 lines of data with nothing but ship to zip codes.
     This worked great and was a huge time saver. I hope karma repays you for your work!

143. Thank you so much! Very cool and helpful. Least I can do is thank you.

144. Thank you so much; you save my day!

145. You’re welcome!

146. Thanks for creating this. Just saved me a ton of time!

147. You’re welcome!

148. This is unbelievably helpful, saved me hours of work!!

149. You’re welcome!

150. […] nice blog by Edel Alon provides the excel sheet here: Zipcode to City, State Excel Spreadsheet • Edel Alon (updated […]

151. Thanks for the link!

152. Hi Edel Alon: I made a blog post including your spreadsheet and how I used it! Thank you so much for such a wonderful resource.

     https://blog.naver.com/merapi95/222982918852
     Here is the link to the blog post.

153. Thank you!

154. Hi Edel! This is amazing! Wondering if you might be providing a 2023 update and how I might get notified if so? I can create it myself.. just thought I might check if you are already contemplating the work with your sources. Really appreciate your work and am so happy I stumbled onto this!

155. Hi Shari, no planned updates for 2023..still working on updating the data. Thanks for visiting.

156. Hi Edel, thank you so, so much for this amazing resource, truly!!

     Also, just a quick fyi – When I pasted a pretty large list of zips (around 9200), it only filled out the info to #537 and wouldn’t automatically do any more of them for some reason. But I was able to just manually “double-click” on the auto fill for the each column and it successfully filled in for the rest, so was not a problem. 🙂

     Figured it was something with my own excel installation, but wanted to let you know, just in case it’s helpful.

     Thank you again Edel!

157. Hi Bob,
     Glad it worked out for you. The reason why it stopped is because if I filled it all the way to the bottom, the file would be humongous. I’m glad it worked out for you. Thanks for visiting.

     Edel

158. Edel-
     Thanks for all your work on this, truly a tiimesaver! Only issue I have, is that Vlookup is looking at that file, so I cant copy and paste the data without having a formula behind it ( if that makes sense)

159. Hi Michael,
     Where are you pasting into?
     Edel

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