While working in Microsoft Excel sometimes we need to format zip code to 5 digits so that we can work more productively. If we change zip codes one by one it’s gonna take a long time to finish. Thus I came up with some simple tricks to save your time. Today in this article, I am sharing with you how to format zip code to 5 digits in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
5 Quick Methods to Format Zip Code to 5 Digits in Excel
In the following, I have described 5 simple and quick methods to format zip code to 5 digits in Excel.
Suppose we have a dataset of some Customer Name, their Address, and Zip Code.
1. Utilizing Format Cells Feature
If you want you can utilize the Format Cells feature to format zip code within a glimpse of an eye. Just select cells and change the format to zip code. That’s it. Follow the instructions below-
- First, choose cells (D5:D11) and press CTRL+1 to open the “Format Cells” window.
- Second, in the new window go to “Special” and choose “ZipCode”.
- Hence, press OK to continue.
- Finally, you will get all the zip code formatted to 5 digits in Excel. Simple isn’t it?
Note: This “Zip Code” feature from “Format Cells” applies only to 5-digit zip codes. Applying this will add a leading zero before the selected zip codes until the zip code become 5 digits. For 5 or more digits of zip code, it won’t work.
2. Apply LEFT Function to Format Zip Code to 5 Digits
In some cases, you will find zip codes with a dash sign (–) and more than 5 digits. In those situations, you can follow the below steps.
- Presently, choose a cell (F5) and apply the below formula-
- The LEFT function will return a given number of values from the given coordinates from a string. Here we have provided 5 as num_chars thus it will count 5 numbers from the left and display them in the output section.
- Next, hit ENTER and drag the “Fill Handle” down to fill.
- In summary, we have successfully formatted zip code to 5 digits in a new column.
Read More: How to Lookup County from Zip Code in Excel
3. Using TEXT Function
Formatting zip codes to 5 digits can also be achieved using the TEXT function in Excel. You have to apply the function and the output will be in your hands.
- To start with, choose a cell (F5) and apply the below formula-
- The TEXT function will return a numeric value to a specified given format. In this formula, we have provided a format which is “00000”. Thus in the output cell, it will display only 5 digits counting from left.
- Next, click ENTER and pull the “Fill Handle” down to fill the other cells in the column.
- Here, we have the final output formatted zip code to 5 digits.
- How to Lookup ZIP Code in Excel (4 Suitable Methods)
- Excel Formula to Change Zip Code to State (5 Examples)
- How to Sort Data by Zip Code in Excel (With Easy Steps)
4. IF Function to Format Zip Code
- Choose a cell (F5) and write the below formula-
- The IF function will go through the logical test (D5<9999). If it’s true then it will add a leading zero before the number unless it will return the cell value as it stands.
- In the same fashion, click ENTER and drag down the “Fill Handle”.
- As a result, we will have the zip code formatted to 5 digits in our worksheet.
5. VBA Code to Format Zip Code to 5 Digits
You can also use VBA code to format the zip code to 5 digits. Go through the steps below-
- From the worksheet, select the zip code and press ALT+F11 to open the “Microsoft Visual Basic for Application” window.
- In the new window click “Module” from the “Insert” option.
- In the new module put the below code and press the “Run” icon-
Option Explicit Sub Zip_code_5_digit() Dim wcell As Range For Each wcell In Selection wcell.Value = Left(wcell.Value, 5) Next End Sub
In this article, I have tried to cover all the methods to format zip code to digits in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the ExcelDemy team, are always responsive to your queries. Stay tuned and keep learning.