Fix leading zeros in zip codes in Excel in 10 seconds

There are sometimes leading zeros in zip codes in the United States.

Meaning that a 5-digit US zip code could be something like 03842.

But Excel assumes numbers shouldn’t start with zeros, and removes them.

There’s an easy way to fix leading zeros in zip codes that takes literally 10 seconds – no matter how many zip codes you’re working with.

The wrong way to go about it is to use an apostrophe. That tells Excel that your cell is text. So if you enter ‘03842, it will read that as text instead of a number, and won’t remove the zero.

But if you have a long list of zip codes, it’s not practical to do that one at a time.

The ten-second solution is to use a formula to fix everything at once.

If you use the formula =TEXT(A2,”00000″), if will convert a number to a five-character text string. If the number is under five digits, it will add leading zeros to make it five characters. Which will make it a proper zip code.

Then just fill the formula down so it converts all of your numbers.

And that’s it. You’re done.

For more quick and easy ways to make your life easier, check out Filters: Excel’s Easy Button, and learn about Freezing Panes.