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 Code to 5 Digits in Excel (5 Easy Methods)

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-

Steps:

  • First, choose cells (D5:D11) and press CTRL+1 to open the “Format Cells” window.

Utilize Format Cells Feature to Format Zip Code to 5 Digits

  • 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?

Utilize Format Cells Feature to Format Zip Code to 5 Digits

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.

Read More: How to Remove Last 4 Digits of Zip Code in Excel (10 Easy Ways)


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.

Apply LEFT Function to Format Zip Code to 5 Digits

  • 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.

Apply LEFT Function to Format Zip Code to 5 Digits

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.

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.

Apply TEXT Function to Format Zip Code to 5 Digits

  • 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.


Similar Readings


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.

IF Function to Format Zip Code

  • 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-

Steps:

  • From the worksheet, select the zip code and press ALT+F11 to open the “Microsoft Visual Basic for Application” window.

VBA Code to Format Zip Code to 5 Digits

  • 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

VBA Code to Format Zip Code to 5 Digits

Explanation of VBA Code:
  • 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, called 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.
  • In conclusion, you will get the result formatted to 5 digits in a new column.
  • excel format zip code to 5 digits


    Conclusion

    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.


    Related Articles

    Wasim Akram

    Wasim Akram

    Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

    We will be happy to hear your thoughts

    Leave a reply

    ExcelDemy
    Logo