Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyphens in Excel Sort
Does anyone know why Microsoft would ignore hyphens and apostrophes in the
sort? The help says it was designed to be consistant with Windows. Our company uses hyphens in certain positions of the product number, so now if we sort by product number, it ignores the hyphens and screws up the list. Any suggestions for getting around this and have a sort use the hyphens? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyphens in Excel Sort
Hi Norm,
Kind of hard to answer a rhetorical question that you already answered, so I'll try for the second part. Sorting in Excel and ASCII itself can be very exasperating. As long as you would not have a problem with hyphens, spaces, exclamation points in the same position. How about making a helper column and sort on it. =SUBSTITUTE(A1,"-","!") Or choose another character to substitute, other than apostrophe or hyphen:: Actually Microsoft Excel help indicates the following order, which is not necessarily my experience for sorting text, in any case you already know about hypen(-) and apostrophe('). 0 1 2 3 4 5 6 7 8 9 ' - (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = A B C D E F G H I J K L M N O P Q R S T U V W X Y Z -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Norm Lundquist" wrote in message ... Does anyone know why Microsoft would ignore hyphens and apostrophes in the sort? The help says it was designed to be consistant with Windows. Our company uses hyphens in certain positions of the product number, so now if we sort by product number, it ignores the hyphens and screws up the list. Any suggestions for getting around this and have a sort use the hyphens? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyphens in Excel Sort
Thanks, David! Using the "Substitute" is the only option that sounds good
right now. As for the first part, I don't understand why it's being consistant with Windows. What part of Windows sorts like that? Anyway, thanks for the reply! "David McRitchie" wrote: Hi Norm, Kind of hard to answer a rhetorical question that you already answered, so I'll try for the second part. Sorting in Excel and ASCII itself can be very exasperating. As long as you would not have a problem with hyphens, spaces, exclamation points in the same position. How about making a helper column and sort on it. =SUBSTITUTE(A1,"-","!") Or choose another character to substitute, other than apostrophe or hyphen:: Actually Microsoft Excel help indicates the following order, which is not necessarily my experience for sorting text, in any case you already know about hypen(-) and apostrophe('). 0 1 2 3 4 5 6 7 8 9 ' - (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = A B C D E F G H I J K L M N O P Q R S T U V W X Y Z -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Norm Lundquist" wrote in message ... Does anyone know why Microsoft would ignore hyphens and apostrophes in the sort? The help says it was designed to be consistant with Windows. Our company uses hyphens in certain positions of the product number, so now if we sort by product number, it ignores the hyphens and screws up the list. Any suggestions for getting around this and have a sort use the hyphens? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyphens in Excel Sort
I don't really know where Windows provides for sorting other than
directories, and the lists similar to Outlook Express where you can sort on From, To, Sent (timestamp), subject, ...etc..., I can't sort anything in Notepad, for instance. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Norm Lundquist" wrote in message ... Thanks, David! Using the "Substitute" is the only option that sounds good right now. As for the first part, I don't understand why it's being consistant with Windows. What part of Windows sorts like that? Anyway, thanks for the reply! "David McRitchie" wrote: Hi Norm, Kind of hard to answer a rhetorical question that you already answered, so I'll try for the second part. Sorting in Excel and ASCII itself can be very exasperating. As long as you would not have a problem with hyphens, spaces, exclamation points in the same position. How about making a helper column and sort on it. =SUBSTITUTE(A1,"-","!") Or choose another character to substitute, other than apostrophe or hyphen:: Actually Microsoft Excel help indicates the following order, which is not necessarily my experience for sorting text, in any case you already know about hypen(-) and apostrophe('). 0 1 2 3 4 5 6 7 8 9 ' - (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = A B C D E F G H I J K L M N O P Q R S T U V W X Y Z -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Norm Lundquist" wrote in message ... Does anyone know why Microsoft would ignore hyphens and apostrophes in the sort? The help says it was designed to be consistant with Windows. Our company uses hyphens in certain positions of the product number, so now if we sort by product number, it ignores the hyphens and screws up the list. Any suggestions for getting around this and have a sort use the hyphens? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting numbers with hyphens in excel | Excel Discussion (Misc queries) | |||
Removing text between hyphens and removing the hyphens | Excel Discussion (Misc queries) | |||
Using two hyphens in front of an Excel function. | Excel Worksheet Functions | |||
Excel sort ( exclude letters include hyphens)? | Excel Worksheet Functions | |||
Does XL2K have "hard hyphens" (non-break hyphens)? | Excel Discussion (Misc queries) |