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.

zip code in excel

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.

Opening Format Cells

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

Selecting ZIP code + 4 format

  • Excel will change the format.

Zip Code + 4 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.

Selecting custom format

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

Custom format applied

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.

Selecting 5-digit custom format

Excel will format the selected numbers.

5-digit custom format

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.

5-digit zip code using LEFT function

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.

5-digit zip code using TEXT function

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.

Inserting new 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

Running VBA code


  • The code takes a range of cells (D5:D11) from a worksheet named “VBA” and truncates the values in those cells to only include the first 5
  • The For Each statement runs a loop and takes the first 5 digits from a “wcell” in “Rng”.
  • Excel will format the zip codes.

5-digit zip code from VBA

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.

Add leading zero format

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

Zip codes with leading zero

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.

Sorting Zip codes in ascending order

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

Sort warning box

Note: We selected Expand the selection option to sort zip codes with addresses in another column.

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

Sorted zip codes

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

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

Sorting zip codes by states

  • Excel will sort the column based on the States.

State-wise sorted zip codes

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.

Filtering zip code based on value

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

 Getting filter options

  • Select Number Filters >> click on Between option.

Setting filter values

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

Putting ranges

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

Sorted zip codes

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.

Zip codes with text

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.

Formatting 5 and 9-digit codes simultaneously


  • [<=99999]00000 – This portion of the code specifies the format for numbers less than or equal to 99,999. It displays a 5-digit number with leading zeros if necessary. For example, the number 123 would be displayed as 00123.
  • 00000-0000 – A semicolon separates this part of the code and specifies the format for numbers greater than 99,999. It displays a 5-digit number followed by a hyphen and a 4-digit For example, the number 123456 would be displayed as 12345-6.

Excel will update the formats.

Updated 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 digits.

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


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

Get FREE Advanced Excel Exercises with Solutions!
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF