Sorting by Zip+4
 

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?
--
Libby

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.
--
Regards,
Dave


"Libby" wrote:

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?
--
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.
--
Libby


"David Billigmeier" wrote:

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.
--
Regards,
Dave


"Libby" wrote:

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?
--
Libby

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.

--
Regards,
Dave


"Libby" wrote:

Hi David:

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

Thanks.
--
Libby


"David Billigmeier" wrote:

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.
--
Regards,
Dave


"Libby" wrote:

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?
--
Libby

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


"David Billigmeier" wrote:

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.

--
Regards,
Dave


"Libby" wrote:

Hi David:

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

Thanks.
--
Libby


"David Billigmeier" wrote:

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.
--
Regards,
Dave


"Libby" wrote:

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?
--
Libby

Sorting by Zip+4
 

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

"Libby" wrote:

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


"David Billigmeier" wrote:

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.

--
Regards,
Dave


"Libby" wrote:

Hi David:

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

Thanks.
--
Libby


"David Billigmeier" wrote:

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.
--
Regards,
Dave


"Libby" wrote:

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?
--
Libby

Sorting by Zip+4
 

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

"Libby" wrote:

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


"David Billigmeier" wrote:

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.

VO, the solution offered by David should work. One way or the other you
need to split up the ZIP and the +4 parts, then a normal sort will do
the job.

Sorting by Zip+4
 

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

"smartin" wrote:
VO, the solution offered by David should work. One way or the other you
need to split up the ZIP and the +4 parts, then a normal sort will do
the job.

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

"Libby" wrote:

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


"David Billigmeier" wrote:

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.