I was getting my U.S. Christmas mailing list in order and noticed a problem on some of my zipcodes. Many of New England's zip codes begin with a zero so when I pulled my addresses into Excel to sort and filter them, Excel dropped the 0 from the beginning of the zipcode so for example, instead of 02186, Excel displayed 2186.
(It did this because it assumed the ZipCode was a number instead of text.)
The fix is easy.
In this case, I clicked in cell I2 and entered =IF(LEN(H2)=5,H2,"0"&H2)
LEN() counts the number of characters. So, I told Excel -if the number of characters in cell H2 total 5 then just display the contents of cell H2. However, if the number of characters in H2 do not total 5 then include a 0 at the beginning of the H2 cell contents. I used an ampersand (&) to tell Excel to join the 0 and the zipcode together however you could also have used the Concatenate() function- I just think the & is faster. Both LEN() and Concatenate() are Text functions.
Once I copied the formula down, I then selected all the zipcodes in Column I, copied them and then did Paste Values so that the formulas were overwritten with the actual numbers. I did that so that when I deleted Column H, my new zipcodes would display. An alternative way would be to just hide Column H.
This IF assumed residential US zipcodes. If you had business zip codes which would have more than 5 characters or were including European postal codes then you would have to change the IF a bit.
You could also format the cells in the Zip Code column as a zip code. This would eliminate having to generate the formula. Or do you see this being used in another way?
ReplyDeleteRoger
If you just convert the numbers to text, it does not put the 0 in front of the zipcode so even if you change it to text 2186 would still display as 2186 instead of 02186. The best alternative would have been to do Text to Columns when you imported the data. LEN is for when you have no control over the importing.
ReplyDelete