Sort by pure text

  • Thread starter Thread starter Frank D. Nicodem, Jr.
  • Start date Start date
F

Frank D. Nicodem, Jr.

I have a column that I want to sort by pure alphanumeric text. However,
some of the cells begin with either an apostrophe (') or a hyphen (-). And
according to the "default sorting order" rules defined by Microsoft (found
in the Help file), apostrophes and hyphens are *ignored* when sorting!

How can I get a *TRUE* sort of a column that has these characters --
especially when they are the leading character?!?!? (I.e., I *want* cells
that begin with an apostrophe to "bubble up" to the top of the sorted
list -- not be buried somewhere else.)
 
According to my Help file (Excel 97) *apostrophes and hyphens are
Not Ignored*



Text and text that includes numbers are sorted in the following order:

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


I personally have neve had any problems with sorting with text
regardless with what text in in each cell
 
I've had trouble when I wanted to sort data which included dashes.

I put this in A1:A3

a1111
a-1-1-1
a-1-1-1b

When I sorted (with no header row), I got this back:
a-1-1-1
a1111
a-1-1-1b

The dash character has an ascii code of 45 (=code("-")). The period is 46. In
the data that I had, there were no dots. I could substitute the period for the
dash and sort by that column.

I used this formula in my helper column:
=SUBSTITUTE(A1,"-",".")
(and dragged down)

So my data (before the sort) now looked like:

a1111 a1111
a-1-1-1 a.1.1.1
a-1-1-1b a.1.1.1b

After I sorted by column B, it looked like:
a-1-1-1 a.1.1.1
a-1-1-1b a.1.1.1b
a1111 a1111

Then I deleted my helper column.

You could do the same technique with the apostrophe (ascii 39). Open
parenthesis "(" is code is 40 and ampersand "&" is 38.

You could use this formula in your helper column if you used the ampersand:

=SUBSTITUTE(SUBSTITUTE(A1,"-","."),"'","&")

(I used xl2002 for this. But the first time I noticed it was at work--that
means it was either xl97 or xl2k.)
 
Glad to see Dave Peterson had a solution. I hadn't realized
that hyphens were ignored but simply thought it to be a single
hyphen that was different. But it is obvious that they are ignored
if you try testing to see if they are ignored.

-
a
a-
a-b
abc
-abc
abd
-abd
axa
ax-b
a-x-b
axc


I'll have to update my sorting.htm page accordingly. Thanks mudraker
for the insight, and Dave for a solution -- as long as you don't also have
periods.

At first glance at the subject I thought a difference between A and a was
wanted and for which there is an option (case sensitive)..

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
 
Frank D. Nicodem, Jr. hu kiteb:
I have a column that I want to sort by pure alphanumeric text.
However, some of the cells begin with either an apostrophe (') or a
hyphen (-). And according to the "default sorting order" rules
defined by Microsoft (found in the Help file), apostrophes and
hyphens are *ignored* when sorting!

How can I get a *TRUE* sort of a column that has these characters --
especially when they are the leading character?!?!? (I.e., I *want*
cells that begin with an apostrophe to "bubble up" to the top of the
sorted
list -- not be buried somewhere else.)
Click to expand...

Create a sort column that you use only for sorting.

=replace(main cell,"-","aaa")

You can nest this to get all teh other special characters, then sort on
this column.
 
Back
Top