Excel provides two special number formats for postal codes, called Zip Code and Zip Code + 4. If these don't meet your needs, you can create your own custom postal code format.
Apply a predefined postal code format to numbers
-
Select the cell or range of cells that you want to format.
To cancel a selection of cells, click any cell on the worksheet.
-
On the Home tab, click the Dialog Box Launcher
-
In the Category box, click Special.
-
In the Type list, click Zip Code or Zip Code + 4.
Notes:
-
These codes are available in the Type list only if the Locale (location) is set to English (United States). Different locales provide different or no special codes in the Type list.
-
If you're importing addresses from an external file, you may notice that the leading zeros in postal codes disappear. That's because Excel interprets the column of postal code values as numbers, when what you really need is for them to be stored and formatted as text. To solve this problem, in step 3 of the Text Import Wizard, click the column containing postal codes, and then, under Column data format, click Text. Click Finish to finish importing your data.
Create a custom postal code format
-
Select the cell or range of cells that you want to format.
To cancel a selection of cells, click any cell on the worksheet.
-
On the Home tab, click the Dialog Box Launcher
-
In the Category box, click Custom.
-
In the Type list, select the number format that you want to customize.
The number format that you select appears in the Type box above the Type list.
For example, to customize the predefined Zip Code + 4 format, select 00000-0000 in the Type list. When you select a built-in number format in the Type list, Excel creates a copy of that number format that you can customize. The original number format in the Type list cannot be changed or deleted, so don't worry about overwriting the original number format.
-
In the Type box, make the necessary changes to the selected number format. For example, if a range of cells contains both five-digit and nine-digit postal codes (ZIP Codes), you can apply a custom format that displays both types of ZIP Codes correctly. In the Type box, type [<=99999]00000;00000-0000
For more information about how to create custom number formats, see Create or delete a custom number format.
Include leading characters in postal codes
You can format a cell or range of cells to display leading characters so that the postal code is preceded by enough characters to fill the cell's width. For example, you can use zeros or dashes to display a postal code as follows: 0000000 98052 or ------- 98052.
-
Select the cell or range of cells that you want to format.
To cancel a selection of cells, click any cell on the worksheet.
-
On the Home tab, click the Dialog Box Launcher
-
In the Category box, click Custom.
-
In the Type list, type *0 followed by the postal code format that you want to use.
For example, for a 5-digit postal code, type *0#####
