Comparing Strings with <

[Jonathan Rynd]

Does anybody know what algorithm Excel uses to compare strings with < and

?

For instance I just created the following table into A1:I9

'-100 '-10 '-1 '0 '00 '1 '10 '100
'-100
'-10
'-1
'0
'00
'1
'10
'100

Note I put a ' before each entry to force it to be a string. I also
could have formatted the cells as text.

Then I put =$A2<B$1 into B2, and filled down and right. I got this:

-100 -10 -1 0 00 1 10 100
-100 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
-10 TRUE FALSE FALSE FALSE FALSE FALSE FALSE TRUE
-1 TRUE TRUE FALSE FALSE FALSE FALSE TRUE TRUE
0 TRUE TRUE TRUE FALSE TRUE TRUE TRUE TRUE
00 TRUE TRUE TRUE FALSE FALSE TRUE TRUE TRUE
1 TRUE TRUE TRUE FALSE FALSE FALSE TRUE TRUE
10 TRUE TRUE FALSE FALSE FALSE FALSE FALSE TRUE
100 TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE


??? It looks like it is taking the absolute value of both sides of the <!

What is going on?

Then I tried this, again using the ' to force it to be strings:

-30 -20 -10 0 10 20 30
-30 FALSE FALSE FALSE FALSE FALSE FALSE FALSE
-20 TRUE FALSE FALSE FALSE FALSE FALSE TRUE
-10 TRUE TRUE FALSE FALSE FALSE TRUE TRUE
0 TRUE TRUE TRUE FALSE TRUE TRUE TRUE
10 TRUE TRUE TRUE FALSE FALSE TRUE TRUE
20 TRUE TRUE FALSE FALSE FALSE FALSE TRUE
30 TRUE FALSE FALSE FALSE FALSE FALSE FALSE

again, it looks like it's taking absolute values!

But it's not just about numbers; something similar happens with text too.

 

[Bernie Deitrick]

Jonathan,

I apolpgize in advance for not being concise or clear - I can't find the
specific reference, which I read a month or so ago - but I believe it has
something to do with how dashes are treated in string comparisons. IIRC,
since they can be used as continuation characters or simply space holders,
they are often ignored.

Why do you want to compare these numbers stringwise anyway?

HTH,
Bernie
MS Excel MVP

 

[Jonathan Rynd]

Why do you want to compare these numbers stringwise anyway?

Yes, I found the part in the docs about dashes being almost always ignored
(apostrophes too, btw).

I didn't have any particular reason for comparing these particular numbers
stringwise; I was just trying to investigate Excel's behavior for comparing
strings with < and the other comparison operators.

 

[hgrove]

Jonathan Rynd wrote...
...

Yes, I found the part in the docs about dashes being almost
always ignored (apostrophes too, btw).

I didn't have any particular reason for comparing these
particular numbers stringwise; I was just trying to investigate
Excel's behavior for comparing strings with < and the other
comparison operators.

Dashes aren't ignored, they're just not considered until all othe
characters are. If you sort "0", "1", "-1", "-0", you get "0", "-0"
"1", "-1" predictably.

If you need text comparisons that don't ignore any characters, you'
need VBA. Here's a simple udf wrapper around VBA's StrComp function.


Function StrCmp(s1 As String, s2 As String) As Double
StrCmp = CDbl(StrComp(s1, s2, vbBinaryCompare))
End Function


which would function similar to SIGN(n1-n2), returning +1 when s1
s2, -1 when s1 < s2, and 0 when they're equal