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.
How to Format Zip Code to 5 Digits in Excel: 5 Easy Methods
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-
Steps:
- 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 becomes 5 digits. For 5 or more digits of zip code, it won’t work.
Read More: How to Find ZIP Code from Address in Excel
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.
Steps:
- Presently, choose a cell (F5) and apply the below formula-
=LEFT(D5,5)Where,
- 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 the zip code to 5 digits in a new column.
Read More: [Fixed] Zip Codes in Excel Starting with 0
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.
Steps:
- To start with, choose a cell (F5) and apply the below formula-
=TEXT(D5,"00000")Where,
- 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.
Read More: How to Sort Data by Zip Code in Excel
4. IF Function to Format Zip Code
In some cases, you might need to add leading zeros to create the zip code to 5 digits. You can use the IF function to do that.
Steps:
- Choose a cell (F5) and write the below formula-
=IF(D5<9999,0&D5,D5)Where,
- 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.
Read More: How to Remove Last 4 Digits of Zip Code in Excel
5. VBA Code to Format Zip Code to 5 Digits
You can also use a VBA code to format the zip code to 5 digits. Go through the steps below-
Steps:
- 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
- Here we have created a Sub Procedure named Zip_code_5_digit.
- Following that, we have declared a variable named “wcell” as a Range.
- After that, call a loop (FOR EACH) to iterate over the selected range (cells).
- Hence, assigning the VBA LEFT function to fetch the 5 digits from the selection part.
Read More: How to Concatenate ZIP Codes in Excel
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, I have tried to cover all the methods to format zip codes into 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.
