How does Excel compare strings?

Question

Expecting Excel to operate lexicographically, but it seems not to.

"1"<"-1" evaluates TRUE despite "-" preceding "1" lexicographically.

This also eliminates the possibility that Excel compares both strings as numbers.

Alternatively, "1"<"-" evaluates FALSE and "1"<"1" evaluates FALSE too.

'-' is 45 (ASCII)

'1' is 49 (ASCII)

Answer 1

This would seem to be a bug, or at least an (undocumented?) feature

Some more examples (red indicated unexpected results)
Notes:

• The 10.00<-10.0 and 10.0<-10.0 results suggest string length can matter is some cases
• VBA does not exhibit this behaviour

And if you want to experiment

A	B	Code(A)	Code(B)	A<B	Left(A,1)<Left(B,1)
'1	'-1	=CODE(A2)	=CODE(LEFT(B2,1))	=A2<B2	=LEFT(A2,1)<LEFT(B2,1)
'0	'-1	=CODE(A3)	=CODE(LEFT(B3,1))	=@A:A<@B:B	=LEFT(A3,1)<LEFT(B3,1)
'2	'-1	=CODE(A4)	=CODE(LEFT(B4,1))	=@A:A<@B:B	=LEFT(A4,1)<LEFT(B4,1)
'-2	'-1	=CODE(A5)	=CODE(LEFT(B5,1))	=@A:A<@B:B	=LEFT(A5,1)<LEFT(B5,1)
'1	'-2	=CODE(A7)	=CODE(LEFT(B7,1))	=@A:A<@B:B	=LEFT(A7,1)<LEFT(B7,1)
'0	'-2	=CODE(A8)	=CODE(LEFT(B8,1))	=@A:A<@B:B	=LEFT(A8,1)<LEFT(B8,1)
'2	'-2	=CODE(A9)	=CODE(LEFT(B9,1))	=@A:A<@B:B	=LEFT(A9,1)<LEFT(B9,1)
'-3	'-2	=CODE(A10)	=CODE(LEFT(B10,1))	=@A:A<@B:B	=LEFT(A10,1)<LEFT(B10,1)
'1	'-	=CODE(A12)	=CODE(LEFT(B12,1))	=@A:A<@B:B	=LEFT(A12,1)<LEFT(B12,1)
'0	'-	=CODE(A13)	=CODE(LEFT(B13,1))	=@A:A<@B:B	=LEFT(A13,1)<LEFT(B13,1)
'2	'-	=CODE(A14)	=CODE(LEFT(B14,1))	=@A:A<@B:B	=LEFT(A14,1)<LEFT(B14,1)
'-3	'-	=CODE(A15)	=CODE(LEFT(B15,1))	=@A:A<@B:B	=LEFT(A15,1)<LEFT(B15,1)
'1	'-88	=CODE(A17)	=CODE(LEFT(B17,1))	=@A:A<@B:B	=LEFT(A17,1)<LEFT(B17,1)
'1000	'-88	=CODE(A18)	=CODE(LEFT(B18,1))	=@A:A<@B:B	=LEFT(A18,1)<LEFT(B18,1)
'9	'-88	=CODE(A19)	=CODE(LEFT(B19,1))	=@A:A<@B:B	=LEFT(A19,1)<LEFT(B19,1)
'-9	'-88	=CODE(A20)	=CODE(LEFT(B20,1))	=@A:A<@B:B	=LEFT(A20,1)<LEFT(B20,1)
'3.0	'-4.0	=CODE(A22)	=CODE(LEFT(B22,1))	=@A:A<@B:B	=LEFT(A22,1)<LEFT(B22,1)
'5.0	'-4.0	=CODE(A23)	=CODE(LEFT(B23,1))	=@A:A<@B:B	=LEFT(A23,1)<LEFT(B23,1)
'10.00	'-10.0	=CODE(A24)	=CODE(LEFT(B24,1))	=@A:A<@B:B	=LEFT(A24,1)<LEFT(B24,1)
'10.0	'-10.0	=CODE(A25)	=CODE(LEFT(B25,1))	=@A:A<@B:B	=LEFT(A25,1)<LEFT(B25,1)
'1.0	'-1	=CODE(A27)	=CODE(LEFT(B27,1))	=@A:A<@B:B	=LEFT(A27,1)<LEFT(B27,1)
'0.0	'-1	=CODE(A28)	=CODE(LEFT(B28,1))	=@A:A<@B:B	=LEFT(A28,1)<LEFT(B28,1)
'2.0	'-1	=CODE(A29)	=CODE(LEFT(B29,1))	=@A:A<@B:B	=LEFT(A29,1)<LEFT(B29,1)
'-2.0	'-1	=CODE(A30)	=CODE(LEFT(B30,1))	=@A:A<@B:B	=LEFT(A30,1)<LEFT(B30,1)
'1E+0	'-1E+0	=CODE(A32)	=CODE(LEFT(B32,1))	=@A:A<@B:B	=LEFT(A32,1)<LEFT(B32,1)
'0E+0	'-1E+0	=CODE(A33)	=CODE(LEFT(B33,1))	=@A:A<@B:B	=LEFT(A33,1)<LEFT(B33,1)
'2E+0	'-1E+0	=CODE(A34)	=CODE(LEFT(B34,1))	=@A:A<@B:B	=LEFT(A34,1)<LEFT(B34,1)
'-2E+0	'-1E+0	=CODE(A35)	=CODE(LEFT(B35,1))	=@A:A<@B:B	=LEFT(A35,1)<LEFT(B35,1)