Sorting – List of Characters (2000)

Topic

New Reply

Hi all,

I was reading the recent OW issues relating to sorting in Word. That reminded me I used to be curious about how Excel orders characters. I mean, if you write in A1

*

then write in A2

/

and finally write in A3

=A1<A2

the result of the formula will be: TRUE. That means that Excel has an internal list of characters and in that list, "*" comes before "/". I haven't tested it yet, but it would be reasonable to think Word makes use of an identical list.
My question, just out of curiosity by now, but it may be useful for an eventual macro in the future, is: is there any documentation revealing the complete list for text characters? Keywords for Googling this one are not very easy. I got all sort of results but the ones I wanted.

Thank you in advance

Reply | Quote

Replies

Hi Diegol

Checking the Excel help file, it sorts 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

Reply | Quote

Hi Tony,

Sorry for not checking the most basic source. I can’t explain why I tend to overlook its potential.

However, the Help file provides a restricted list. For example, using the method in my post above, “

Reply | Quote

Are you concerned with sorting or comparing? You mention sorting, but all your examples are comparison. They are 2 entirely different techniques.

When excel sorts Ascending:
Numbers (in numerical order)
Text (alphabetical order, by ascii code )
Logical (false, then true)
Errors (in the order they are entered, they are not sorted within the group: this is what excel means by equal)
Blanks

When excel sorts Descending:
Errors (in the order they are entered, they are not sorted within the group: this is what excel means by equal)
Logical (true, then false)
Text (reverse alphabetical order, by ascii code)
Numbers (reverse numerical order)
Blanks

When you compare within a type:
number are compared as numbers

Text compares based on the ascii code (case is ignored in excel, but is taken into account in VB)

With logical True > False

With errors, you get the first error.

If you try to compare between types you get like the sort order: Logical > Text > numbers. If you try in VB you generally get a type mismatch error. You can convert to the same type (text is the most general) so that the logical TRUE becomes the text “TRUE” and it is compared as text.

Steve

Reply | Quote

Hello Steve.

Yes, in fact my concern is with comparing. I would like to know, for logical “=”, “<", "=”, “”, “>” operations, which character comes first and which last.

When I looked at the list Tony provided from the Help, I thought it was somewhat “short” (very few characters). So I checked with an ASCII code table and it kind of made sense… save for the fact that in Excel “?” lies amidst “!” and “z”, whereas in the (extended) table in the link, “?” (#168) comes after both “!” (#33) and “z” (#122 or #90 capitalized).

In any case, as I said in my first post, I’m just being curious at the moment, and since it doesn’t seem to be so simple as I thought I don’t want you guys to bother any further. Should I really come up with an application for such detailed comparisons I’ll let you know and we’ll keep investigating.

Thank you very much for your time

Reply | Quote

The question mark (?) is char(63) not 168, putting it beween the “>” and the “@”. The 168 in the extended table is an upside down question mark.(

Reply | Quote

Sorry Steve, my mistake in my last post.

What I really intended to type was “

Reply | Quote

I guess it is not sorted by ASCII code. Perhaps it is by ANSI or Unicode…

Steve

Reply | Quote