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

We’ll cover a common scenario where we might need to remove the last 4 digits of a zip code. Note that the zip codes and the addresses are randomly generated. That’s why they are not consistent throughout the article.

Download the Practice Workbook

Zip Code in Excel.xlsm

Display Numbers in the Zip Code Format

Method 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 the range D5:D11 to format and click the dialog launcher arrow of the Number group shown in the image or press Ctrl + 1.

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

Excel will change the format.

Method 2 – Create a Custom Postal Code Format+

Select the range D5:D11 and move to Format Cells.
Choose Category as Custom and insert the following format in the Type box.

00000-0000

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

Method 1 – Utilizing the Format Cells Feature

Select D5:D11 and go to the Format Cells window, then use the following format and click OK.

00000

Excel will format the selected numbers.

Method 2 – Apply the LEFT Function to Extract and Format Zip Code to 5 Digits

Insert the following formula in cell D5 and use the Fill Handle to AutoFill up to D11.

=LEFT(C5,5)

Method 3 – Excel TEXT Function to Format Zip Codes

Insert the following formula in D5 and use the Fill Handle to AutoFill up to D11.

=TEXT(C5,"00000")

Method 4 – VBA Code to Extract and Format Zip Codes to 5 Digits

Press Alt + F11 to bring the Visual Basic Editor.
Select the Insert tab from the Editor and click Module.

Insert 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

Explanation:

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.

Insert Leading Characters in Postal Codes

Select D5:D11 and go to the Format Cells box.
Use the following format and click OK.

00000

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

Sort Zip Codes in Excel

Method 1 – Sorting ZIP Codes in Ascending or Descending Order

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

You may get a Sort Warning.
Select Expand the selection and click OK.

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.

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

Method 2 – Sorting ZIP Codes by State

Select the range B5:B11 that contains the States.
Go to the Data tab and select the A to Z option for sorting.

Excel will sort the column based on the States.

Method 3 – Filtering Zip Codes

We want to get the zip codes that lie between 97000 and 100000.

Select the range B4:C11.
Go to the Data tab and select the Filter option.

Excel will activate the Filter. Click on the down arrow icon shown in the image.

Select Number Filters and click on Between.

A Custom Autofilter box will appear.
Enter the ranges in the boxes and check the And criteria, then 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.

Showing 5- and 9-Digit Zip Codes Simultaneously

Select range D5:D11 and open the Format Cells box.
Choose Custom format and insert the following format:

[<=99999]00000;00000-0000

Explanation:

[<=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.

Remove the Last 4 Digits of a Zip Code

We have a 9-digit code and want to remove the last 4 digits from the number.

Apply the following formula in D5 and use the Fill Handle to AutoFill up to D11.

=MID(C5,1,5)

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

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

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.

How can I sort zip codes in Excel correctly?

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.

Can I plot zip codes on a map within Excel?

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.

Zip Code in Excel: Knowledge Hub

<< Go Back to Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!