Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Advice sought: Multiple column sorting | Excel Worksheet Functions | |||
Sorting 1, 1A, 2, 2A, 3, 4, 4A, 4B . . . | Excel Discussion (Misc queries) | |||
SORTING question | New Users to Excel | |||
Adding a KeyID column for sorting | New Users to Excel |