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.
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.
- 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.
In the E7 cell write the following formula and press ENTER.
both D7 and E7 will show the same value.
Now, selecting E7, click right on the mouse and select Format Cells from the context menu.
After that, select Special from the Number bar and select ZIP Code, and press OK.
Thus, we get a 5-digit zip code in E7 with a zero at the front.
Now, drag the E7 cell downward to get 5 digit Zip Code for all other cells.
Now, for the 9-digit system, we will use the TEXT function. write the below formula in the F7 cell and press ENTER.
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.
Read More: How to Find ZIP Code from Address in Excel
2. Shortening of Zip Codes in Excel
- First, select C5.
- Then write the following formula and press ENTER.
We will have the first 5-digit Zip Code.
- Now drag the C5 cell downward to get all the desired 5-digit zip codes.
For the last 4 digit zip code,
- Select D5 and write the formula and press ENTER
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.
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.
We’ve attached a Practice sheet where you can practice the methods.
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.
- How to Sort Data by Zip Code in Excel
- How to Remove Last 4 Digits of Zip Code in Excel
- How to Concatenate ZIP Codes in Excel