How to Format Zip Codes in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will show different methods to format zip codes in Excel. The zip codes can vary from country to country. There might be a need for the zip code to be of a specific digit.

How to Format Zip Codes in Excel: 2 Easy Methods

Before starting the methods let’s have a look at our dataset. Here, we have an Employee list with their names and respective states, and Zip Codes.

dataset for Zip Code format

The dataset here is only for example purposes, so we are working with dummy data.

1. Format by Adding Zeros to Zip Codes

In this section, we’re going to show you how you can add zeros ahead of the zip code to format in the way you want. Let’s start.

Step 1:

  • First, let’s add two columns next to the Zip Codes column.

Here, we are about to show you two cases that’s why we added two columns. Feel free to use your desired version.

Step 2:

In the E7 cell write the following formula and press ENTER.


adding two column

both D7 and E7 will show the same value.

Step 3:    

Now, selecting E7, click right on the mouse and select Format Cells from the context menu.

Formatting cell

After that, select Special from the Number bar and select ZIP Code, and press OK.

selecting Zip Code

Thus, we get a 5-digit zip code in E7 with a zero at the front. 

Step 4:

Now, drag the E7 cell downward to get 5 digit Zip Code for all other cells.

dragging of E7

Step 5:

Now, for the 9-digit system, we will use the TEXT function. write the below formula in the F7 cell and press ENTER.


formula for Text

Note: In the above formula, we used 9 zeros for a 9-digit zip code. You can use as many digits as suits you.

Then we will have our 9-digit zip code.

After that, hold and drag F7 downward. We will get a 9-digit zip code for all the cells.

dragging for all Zip Codes

Read More: How to Find ZIP Code from Address in Excel

2. Shortening of Zip Codes in Excel

For this method, we have a dataset of 9-digit zip codes. We can divide these 9-digit zip codes into 5 and 4-digit zip codes separately.  For that, we will use the LEFT and RIGHT functions.

dragging for all Zip CodesStep 1:

  • First, select C5.
  • Then write the following formula and press ENTER.

We will have the first 5-digit Zip Code.

use of Left function

  • Now drag the C5 cell downward to get all the desired 5-digit zip codes.

dragging for all the cells to format Zip Code

Step 2:

For the last 4 digit zip code,

  • Select D5 and write the formula and press ENTER

use of Right function to format Zip Code

And we will have our last 4-digit zip code.

Then hold and drag the D5 cell downward to get 4-digit zip codes for all the cells.

dragging for other cells to format zip code

Read More: [Fixed] Zip Codes in Excel Starting with 0

Things to remember

The equations in this article can always be modified according to your requirements. You have to choose your cell according to your dataset.

Practice Section

We’ve attached a Practice sheet where you can practice the methods.

practice. format by putting 0 ahead of codespractice. shortening of Zip Codes

Download Practice Workbook


That’s all for the article. We have shown you a couple of handy ways to format Zip Codes in Excel. Hope you will find these helpful. If you have any queries related to these methods, please leave a comment.

Related Articles

<< Go Back to Zip Code in Excel | Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sourav Kundu
Sourav Kundu

Sourav Kundu, BSc, Naval Architecture & Marine Engineering, Bangladesh University of Engineering and Technology, is a dedicated technical content creator of the ExcelDemy project. He has a keen interest in Excel and he leverages his problem-solving skills to provide solutions on user interface with Excel. In his position as an Excel & VBA Content Developer at ExcelDemy, Sourav Kundu not only adeptly addresses challenging issues but also demonstrates enthusiasm and expertise in navigating complex situations. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF