Sourav Kundu
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.
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
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.
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.
Step 4:
Now, drag the E7 cell downward to get 5 digit Zip Code for all other cells.
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”)
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 Create Zip Code with Excel Formula (6 Easy Ways)
Similar Readings
- How to Change Zip Code to State by Excel Formula (5 Easy Ways)
- Lookup ZIP Code in Excel (4 Suitable Methods)
- Sort Data by Zip Code in Excel (With Easy Steps)
- How to Auto Populate Zip Codes in Excel (3 Easy Ways)
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.
Step 1:
- First, select C5.
- Then write the following formula and press ENTER.
=LEFT(B5,5)We will have the first 5-digit Zip Code.
- Now drag the C5 cell downward to get all the desired 5-digit zip codes.
Step 2:
For the last 4 digit zip code,
- Select D5 and write the formula and press ENTER
=RIGHT(B5,4)
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: 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.
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.
