Sorting of strings containing the underscore character 2

[Deniall]

I have just discovered some "inconsistent" behaviour between Excel's spreadsheet environment and its VBA environment.[&nbsp;] It involves the sort order of characters.[&nbsp;] In the spreadsheet, it is TRUE that ("_"<"X"), whereas in VBA it is FALSE.[&nbsp;] This anomaly tripped me up because I have a column of alphanumeric codes which I sort under spreadsheet control, then pass to VBA where I scan down it item by item.[&nbsp;] The codes do not contain any numeric characters, but they do occasionally contain an underscore character(_) because I have a deep psychological predisposition to avoid embedded blanks.

I'm not sure whether I am posting this as a query, or as a warning.
But it is certainly a significant PIA.

 

[strongm]

This is documented - although perhaps not clearly enough. The documentation states that sheet logical text comparisons (unlike VBA) are case insensitive, but doesn't properly explain that this means that alphabetic characters are therefore treated as lowercase, so the actual evaluation being carried out is "_"<"x". If you want a case-sensitive logical test, use the IF function in combination with EXACT

 

[SkipVought]

hi,

I'm guessing that VBA uses strict ASCII values.

You'll see that in VBA
[tt]
"_" < "X" is FALSE
"_" < "x" is TRUE
[/tt]

I did a test in my Excel sheet and sorted the characters from ASCII 32 thruogh 128. Look where "X" and "x" fall...
[pre]
ASCII CHAR
127 
39 '
45 -
32
33 !
34 "
35 #
36 $
37 %
38 &
40 (
41 )
42 *
44 ,
46 .
47 /
58 :
59 ;
63 ?
64 @
91 [
92
93 ]
94 ^
95 _
96 `
123 {
124 |
125 }
126 ~
128 €
43 +
60 <
61 =
62 >
48 0
49 1
50 2
51 3
52 4
53 5
54 6
55 7
56 8
57 9
65 A
97 a
66 B
98 b
67 C
99 c
68 D
100 d
69 E
101 e
70 F
102 f
71 G
103 g
72 H
104 h
73 I
105 i
74 J
106 j
75 K
107 k
76 L
108 l
77 M
109 m
78 N
110 n
79 O
111 o
80 P
112 p
81 Q
113 q
82 R
114 r
83 S
115 s
84 T
116 t
85 U
117 u
86 V
118 v
87 W
119 w
88 X
120 x
89 Y
121 y
90 Z
122 z
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Deniall]

Thanks, gents.
Armed with the info the two of you have provided I will be able to find a way out of my conundrum.