Zip Code in Excel (Insert, Display, Format & Remove)

In this article, you will learn the process of formatting zip codes in Excel. This includes the application of ready-made postal code formats or the creation of custom formats to fit your needs.

Then, we’ll show you how to format zip codes to the standard 5-digit format using basic tools and functions.

You will also learn how to sort zip codes in ascending or descending order and filter them by state. We’ll also address the challenge of handling zip codes that contain text and teach you how to display both 5-digit and 9-digit zip codes together.

Finally, we’ll cover a common scenario where you might need to remove the last 4 digits of a zip code. By the end of this guide, you will format and manage zip codes efficiently in Excel. Let’s get started and simplify the process of zip code formatting in Excel.

Please note that the zip codes and the addresses are randomly generated. That’s why they are not consistent throughout the article.

Download Practice Workbook

Download the practice book here.

Display Numbers in Zip Code Format

Zip codes have some distinct formats and Microsoft Excel has many of them by default. This section will cover how to display some random numbers in a zip code format.

1. Apply a Predefined Postal Code Format to Numbers

Excel has 2 dedicated formats for zip codes: Zip Code, and Zip Code + 4. You will get them from the Format Cells box. Let’s apply the Zip Code + 4 format first. This will convert any number to a 9-digit code.

• Select range D5:D11 to format >> click the arrow sign of the Number group shown in the image.

• Format Cells box appears.
• Select Special as Category >> Zip Code + 4 as Type.
• Click OK.

• Excel will change the format.

2. Create a Custom Postal Code Format

Apart from using a built-in Special format, you can create your own customized format for zip codes.

• Selecting range D5:D11, move to Format Cells.
• Choose Category as Custom >> write down the following format in the Type box.

This format will convert any number that has less than 9 digits to a 9-digit zip number by adding zeros up front.

Format Zip Code to 5 Digits in Excel

We often require zip codes to be formatted to 5 digits. Excel offers some effective methods to do the task.

1. Utilizing Format Cells Feature

• Select D5:D11 and move to Format Cells window >> write down the following format >> click OK.

Excel will format the selected numbers.

2. Apply LEFT Function to Extract and Format Zip Code to 5 Digits

You can apply the LEFT function to format zip codes to 5 digits.

• Write down the following formula in cell D5 >> use Fill Handle to AutoFill up to D11.

3. Excel TEXT Function to Format Zip Code

The TEXT function is also useful in formatting zip codes.

• Write down the following formula in D5 >> use Fill Handle to AutoFill up to D11.

4. VBA Code to Extract and Format Zip Code to 5 Digits

Next, I will show you a simple VBA Macro that can format a range of numbers to 5-digit zip codes.

• Press ALT + F11 to bring the Visual Basic Editor >> select the Insert tab from the Editor >> click Module.

• Then, write down the following code and run it.

Sub Zip_code_5()
Dim wcell As Range
Dim Rng As Range
Set Rng = Sheets("VBA").Range("D5:D11")
    For Each wcell In Rng
        wcell.Value = Left(wcell.Value, 5)
    Next wcell
End Sub

• Excel will format the zip codes.

Insert Leading Characters in Postal Codes

Excel avoids leading zeros in a cell by default. But you may have some leading zeros in a zip code. So you have to do some custom formatting to include leading zeros or any character.

• Select D5:D11 and go to the Format Cells box.
• Write down the following format >> click OK.

• Excel will add leading zeros where necessary. For instance, Excel converts 926 to 00926.

Sort Zip Codes in Excel

1. Sorting ZIP Codes in Ascending or Descending Order

The most basic way to sort zip codes in Excel is to use the Sort feature. To sort the zip codes in ascending order,

• Select range C5:C11 that contains your ZIP codes >> click on the Data tab >> select Sort A to Z.

• You may get a Sort Warning.
• Select Expand the selection option >> click OK.

• This will sort your ZIP codes based on their numerical value, rather than their text value.

• Similarly, by choosing “Z to A” from the Data tab, you can sort the zip codes in descending order.

2. Sorting ZIP Codes by State

You can sort zip codes by State too. In this case, Excel will sort the codes based on the text value.

• Select range B5:B11 that contains the States >> go to the Data tab >> select the A to Z option for sorting.

• Excel will sort the column based on the States.

3. Filtering Zip Codes

You can use the Filter feature to filter zip codes. Suppose I want to get the zip codes that lie between 97000 and 100000.

• Select range B4:C11 >> go to the Data tab >> select the Filter option.

• Excel will activate the Filter Now, click on the down arrow icon shown in the image.

• Select Number Filters >> click on Between option.

• A Custom Autofilter box will appear.
• Write down the ranges in the box >> check And criteria >> click OK.

• Excel will filter all the zip codes in the range.

Zip Codes with Text

Zip codes may be composed of text instead of numbers. This is observed in the British Zip code system. The following image shows some examples.

Showing 5 and 9 Digit Zip Codes Simultaneously

• In case you have a list of zip codes that have both 5 and 9 digits, you can show them simultaneously. Select range D5:D11 and enter it into the Format Cells box.
• Choose Custom format and insert the following format.

Excel will update the formats.

Remove Last 4 Digits of Zip Code

Sometimes, you have a 9-digit code and you want to remove the last 4 digits from the number. You can easily do it using the MID function.

• Write down the following formula in D5 >> use Fill Handle to AutoFill up to D11.

• Excel will remove the last 4 digits.

Things to Remember

• Zip codes are typically stored as text in Excel to preserve leading zeros.
• Zip code formats vary from one place to another.
• Be careful about leading or trailing characters that may be present in zip codes, such as hyphens or special characters.

Frequently Asked Questions

1. How can I validate zip codes in Excel to ensure they are correct?

Ans: You can use data validation rules or custom formulas to validate zip codes. For example, you can create a custom formula using regular expressions to check if the format matches a valid zip code pattern.

2. How can I sort zip codes in Excel correctly?

Ans: When sorting zip codes, ensure that Excel treats them as text rather than numbers. Select the range of zip codes, go to the “Sort” option, and specify that the data should be sorted as text.

3. Can I plot zip codes on a map within Excel?

Ans: Excel doesn’t have built-in mapping capabilities. However, you can use add-ins or external tools to plot zip codes on maps by importing the zip code data and mapping it using geographical boundaries.

Conclusion

In conclusion, working with zip codes in Excel can be made easy by using simple techniques. You can format zip codes to have a consistent appearance by using predefined formats or creating your own.

Sorting zip codes in either ascending or descending order and filtering them by state can help you organize and analyze the data more effectively.

Dealing with zip codes that have text can be solved by using functions to extract the relevant information. Lastly, the guide also covers situations where you might need to remove or change parts of a zip code.

By following these steps, you’ll be able to format and manage zip codes in Excel without any hassle, making your work more efficient.

Zip Code in Excel: Knowledge Hub

<< Go Back to Number Format | Learn Excel