Simple Excel Trick - Zip Codes

I am not sure if I knew this one already, but it's a simple trick if all your zip code data is only 5 numbers in length. Warning it does not work if you have the extra digits in your zip code with a - in it.

So lets say your zip code is 04567, you likely know if Excel it will be turned into 4567. Well you can use the text function to fix that problem.

So lets say your zip code is in cell A1, your formula would be =text(A1,"00000")

And now it will change from 4567 to 04567.

I added this update to the original blog post. I found the another more eloquent fix to zip code issues. Test this fix on your zip codes and see if it does not fix it. I have not been able to come up a scenario that it does not fix. Zip code is in cell A4 in the formula below. =IF(IF(ISNUMBER(FIND("'",A4)),1,0)=1,RIGHT(A4,5),LEFT(A4,5)) Stay tuned, I am going to be posting my favorite Excel hacks in future blog posts.