Format Excel Column with Mixed 5- and 9- Digit Zip Codes

If you are formatting a column to display in zip code format in Excel but have been stymied up until now because it contains both 5- and 9-digit zip codes, then you're finally in luck -- you've just found a way to format the column to accommodate both formats.  Here's your solution: in Excel versions prior to 2007, go to on the menu bar, click on Format > then Cells > then the Number tab > then select Custom in the box, and finally enter a custom format of

[>99999]0000-0000; 00000

The same formula works in Excel 2007 and up, but you access the Format dialog by going to the Home tab > Cells label > Format > Format Cells > Number tab > Custom.

1 comment:

  1. Thanks but you missed a 0
    [>99999]0000-0000; 00000
    should be
    [>99999]00000-0000; 00000
    or you cut off the leading 0 in a 9 digit zip code

    ReplyDelete

Note: Only a member of this blog may post a comment.