Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Sort Data by Zip Code in Excel (With Easy Steps)

There are times when a zip codebased mailing is necessary. Sometimes a mixed-up (5digit and 9digit) number causes complications. Excel can be a useful tool for sorting data, especially in this case where two forms of the zip code can be found, creating complications. So now let’s look at how to sort by zip code in Excel.


Download Practice Workbook

To practice by yourself, download the following workbook.


Step-by-Step Procedures to Sort Data by Zip Code in Excel

To demonstrate the steps, we will use a dataset containing the First and Last names of some people, as well as their Zip Codes. Column B includes the first name, Column C the last name, and Column D the zip code. Because there are two types of Zip Codes, in STEP 1 you will encounter the challenge and then sort by Zip Code. Let’s go through the steps below to see how it works.

how to sort by zip code in excel


STEP 1: Add Helper Column

  • At first, we need to encounter the problem of 5 and 9-digit Zip Codes.
  • For that purpose, add another column next to the Zip Code column.
  • You can rename the column name to “Helper”.

Add Helper Column


STEP 2: Insert Formula

  • Write the formula in the Cell E5 cell like this:
=LEFT(D5,5)

Insert Formula

Here, we have used the LEFT function. Cell D5 denotes the address’s zip code, and 5 denotes that we are taking the first five digits. Thus, we are taking the first five digits of each Zip Code using this formula.

  • Secondly, press ENTER to get the output.
  • Thirdly, use the Fill Handle by dragging down the cursor while holding it at the right-bottom corner of Cell E5.

  • So, we will get the outputs like the picture below.

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


STEP 3: Open Sort Window

  • Now we will select the “Helper” column, which we will now sort.

Open Sort Window

  • For starting the sorting process, go to the Data tab in the ribbon and select Sort.


Similar Readings


STEP 4: Expand Selection

  • A Sort Warning message box will occur.
  • Now, select Expand the selection and click Sort.

Expand Selection


STEP 5: Choose Column for Sorting

  • Next, a Sort box will pop up.
  • Click on the drop-down icon in the Sort by box.

Selecting Column for Sorting

  • We have to choose the column containing only the separated 5digit zip codes.
  • So, we will choose Helper column and press OK.

Read More: How to Format Zip Code to 5 Digits in Excel (5 Easy Methods)


STEP 6: Select Sorting Option

  • Another message box will appear.
  • Select Sort anything that looks like a number, as a number and click OK.


Final Output

  • Following that, we can see that the data has been sorted by Zip Code.

Final Output

  • We no longer need the “Helper” column because it is not part of our main dataset and thus do not want to keep it.
  • So, you can Hide the Helper column.
  • In the end, this is our final result, in which we sorted the personal information by Zip Code.


Conclusion

In this article, we have demonstrated step-by-step procedures for sorting by zip code in Excel. There is a practice workbook at the beginning of the article. Go ahead and give it a try. To read similar articles, check out the ExcelDemy website. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.


Related Articles

Sudipta Chandra Sarker

Sudipta Chandra Sarker

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel. Here I will be posting articles related to this. My educational degree is BSc in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, Bangladesh. I have a great interest in research and development. I always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo