Excel Formatting for Zip Codes and Phone Numbers

(make sure to download the sample workbook so you can follow along)

Sections:

Displaying Zip/Postal Codes in Excel

Displaying Phone Numbers in Excel

Custom Phone Number Formatting

Zip Codes with Text

Other Tricks

Displaying Zip/Postal Codes in Excel

We need to add a special number format to a cell in Excel to display some postal codes correctly.

Here is an example of a code we want:

01234

This is a good example because, when we put it into Excel, it will end up like this:

To make this zip code display correctly we must change the formatting for the cell.

Right-click the cell and select Format Cells...

Then, go to the Number tab, select Special from the Category section and then select Zip Code from the Type section and hit OK.

Now, let's look at that cell again:

Now, you have your leading zero back!

Note, however, that when you edit the cell, there is no leading zero; it will only appear in the spreadsheet when you are not editing the cell.  You can see this in the above image when you look to the Formula Bar and see only 1234.

Displaying Phone Numbers in Excel

Just like the above example, we can add a number format to the cell to make phone numbers appear correctly or at least in a way that is easier to read.

Let's say this is our number:

313-224-0034

If we enter it into Excel, it might look like this:

Most people don't take the time to input dashes when they type a number.

We can use a number format in Excel to automatically add the dashes for us.

Right-click the cell and select Format Cells

Then, go to the Number tab, select Special from the Category section and then select Phone Number from the Type section and hit OK.

Then we get this:

We get a nice and easy-to-read format.

If you look to the Formula Bar though (same as double-clicking the cell to edit it) you will see that this formatting has not been added to the contents of the cell, just its output.  So, when you double-click the cell, you will still see just the numbers.

But, this doesn't work for every way to enter a phone number and this is where custom formats come in handy, so keep reading.

Custom Phone Number Formatting

Now, Excel only has a few default options for special number formatting and this is quite limiting (they should have just programmed-in tons of them) BUT, we have custom number formatting that allows us to do this.

Let's say that we want to add the country code to the number so it would now be like this:

1-313-224-0034

Also, we don't want parenthesis.  For this, we must add a custom number format.

Right-click the desired cell and click Format Cells...

Go to the Number tab and select Custom from the Category section.  Then, we enter a custom number format under where it says Type:

The custom format entered is:

Once you hit OK, this is the final result:

I suggest playing around with this example until you get what you are looking for and then keep that custom number format safe somewhere. Custom number formatting can be really confusing and it feels like another language you have to learn. 

Zip Codes with Text

Some zip/postal codes include text, like the ever confusing British system, which can look like this:

NW1 3DP

Since this is just text, you don't need a special number format for it.  It doesn't matter if you start with a 0 (zero) or not because it will be retained.

Other Tricks

In the end, if all else fails, you can always just enter the data as text and be done with custom formatting.

This can cause other issues when you need to perform calculations on the data but, if there is no other option available, put an apostrophe in front of the numbers and all leading zeroes and anything else that you enter will stay in the cell.

This is not the best option, but it will work for display purposes.