Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.


Download Practice Workbook


2 Methods to Format Zip Codes in Excel

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.

=D7

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.

=TEXT(D7,”000000000”)

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 Create Zip Code with Excel Formula (6 Easy Ways)


Similar Readings


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.
=LEFT(B5,5)

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
=RIGHT(B5,4)

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: How to Remove Last 4 Digits of Zip Code in Excel (10 Easy Ways)


Things to remember

The equations in this article can always be modified according to your requirement. 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


Conclusion

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

Sourav Kundu

Sourav Kundu

Bio: Hi, I am Sourav Kundu. I live in Adabor, Dhaka. I graduated in Naval Architecture and Marine Engineering from BUET. I am really excited to be a part of SOFTEKO family. I want to develop my research skill and find innovative solutions for the given problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo