MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Zip Code Problems

Posted by Ajay on October 26, 2001 10:58 AM

I've got a list of 40,000 zip codes and I'm tryingf to link them to

> using VLOOKUP. The problem is that the source list is formatted as general

> and actually contains a "0" before Zip codes that begin with a "0". My

> list contains ZIP codes that while the value in the cell may be1928, for

> example, I've got the cells formatted as Special>Zip Code, so it displays

> as )1928. So, I trimmed the preceeding zeros off of my source list and

> formatted the cells as Special>Zip Code. NOW, here's the problem. In order

> to get the cells to actually be formatted like Zip Codes, I have to

> double-click (or edit the cell by pressing F2) the hit enter. I don't have

> to change the cell in any way, just click in it, then click out of it,

> Excel formats it appropriately. As you can imagine, I'm not too excited

> about doing this 40,000 times and I can't figure out how to automate the

> process through a macro.


> Any ideas???


Posted by Damon Ostrander on October 26, 2001 11:53 AM

Hi Ajay,

You really didn't need to trim the leading zeros off your source list. You could have used in VLOOKUP in place of the cell reference (say it is B4) TEXT(B4,"00000"), which would convert it to text corresponding to the zip with a leading zero.