Share via

How do I sort zip codes when some have 5 digits and other are 9 digits?

Anonymous
2012-05-23T01:38:08+00:00

I am trying to sort a data list by zip code in ascending order.  Some of the zip codes are 5 digits and others have 9.  Basically some are just the 5 digits and some are the 5 digits with a hyphen and 4 additional numbers.  I want to sort by 5 digits only instead of separating the 5 digit zips from the 9 digit zips.  I have already formatted the cells as text.  Oh and I don't want to remove the last 4 digits from the longer zip codes.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2012-05-23T14:21:22+00:00

    The easiest way is to set up another column to sort by. Use a formula in that column to add the 4 digit zip extensions to the cells that don't have it. the formula would be something like:

    =if(len(a1)=5,a1&"-0000",A12)

    fill this down for all rows. then sort on this column.

    4 people found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-05-23T14:21:42+00:00

    You either have to put zip and zip+4 in separate columns(Rows).

    or if you put all together when sorted  it will sort zip first and then Zip-+ 4 will be after They will be sorted next

    Select the (highlight) the entire section you want sorted information under Name address, city, state, zip

    The go to Data menu and choose column to sort by Zip.

    it will be sorted first by Zip and Zip+4 All in one step.

    I know of no other way. You could do AS I suggested in firt line make a separate column for zip and zip+4

    Then sort first by Zip and then Zip+4.

    0 comments