Sudipta Chandra Sarker
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.
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.
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”.
STEP 2: Insert Formula
- Write the formula in the Cell E5 cell like this:
=LEFT(D5,5)
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.
- For starting the sorting process, go to the Data tab in the ribbon and select Sort.
Similar Readings
- Create Zip Code with Excel Formula (6 Easy Ways)
- How to Lookup County from Zip Code in Excel
- Map Excel Data by ZIP Code (2 Easy Methods)
- How to Format Zip Codes in Excel (2 Easy Methods)
STEP 4: Expand Selection
- A Sort Warning message box will occur.
- Now, select Expand the selection and click Sort.
STEP 5: Choose Column for Sorting
- Next, a Sort box will pop up.
- Click on the drop-down icon in the Sort by box.
- We have to choose the column containing only the separated 5–digit 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.
- 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.
