Sorting by Zip+4

[Libby]

We have an excel spread sheet that has one column which contains zip codes.
About 60% of them are Zip +4 but the rest of them are only 5 digit zip codes.
When we want them to sort so they sort by the first five digits only. It
would be nice if the next 4 were in ascending order also but not necessary.
Any Ideas?

 

[David Billigmeier]

Use the Sort feature... Data->Sort. Make sure both your Zip 5 and Zip 4
columns are selected. Then, in the first box titled "Sort by" choose the
column with the Zip 5. Then, in the second box titled "Then by" choose the
Zip 4 column. This will wort the Zip 5 followed by Zip 4. You can choose
either Ascending or Descending.

 

[Libby]

Hi David:

The Zip's are posted like this into 1 column
77004 or 77004-1265. They are not in two columns. Suggestions?

Thanks.

 

[David Billigmeier]

Data->Text to Columns, choose "Delimited", hit next, choose "Comma" as the
delimiter.

This will split those Zip 5's and Zip 4's into separate columns. You can
then sort them using the process in my previous post. To get them back to
the xxxxx-xxxx format you can use the CONCATENATE() function:

=CONCATENATE("77004","-","1265) will output 77004-1265.

 

[Libby]

Thank you, I will try this but I can see the logic and I know this will fix it.

 

[VO]

Hi Libby-
I'm having the same trouble sorting the the 5 digit and 9 digit zip codes.
Any chance you found a solution?

 

[VO]

smartin-
David's solution was correct. Thanks for the confimation.