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

Get FREE Advanced Excel Exercises with Solutions!

There are times when a zip code-based mailing is necessary. Sometimes a mixed-up (5-digit and 9-digit) 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.


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

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

Read More: How to Find ZIP Code from Address in Excel


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: [Fixed] Zip Codes in Excel Starting with 0


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.


STEP 4: Expand Selection

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

Expand Selection

Read More: How to Format Zip Codes in Excel


STEP 5: Choose a 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


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.


Download Practice Workbook

To practice by yourself, download the following workbook.


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. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.


Related Articles


<< Go Back to Zip Code in Excel | Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sudipta Chandra Sarker
Sudipta Chandra Sarker

Sudipta Chandra Sarker, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked on the ExcelDemy project for over a year. For ExcelDemy, he has authored 42 articles and reviewed over ten articles. He is employed as a junior software developer at the moment. He aims to create various useful Microsoft Office Add-ins, extending the functionality of Office programs. His interests span Microsoft Office Suites, Data Science, VBA, VB.NET, ASP.NET, C#, Excel, and Desktop... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo