Macro String Compare is incorrect (40G_EL_TEST_01 is greater than 40GC-TX-ASSY---)

[javelinmania]

I manually sort a column of data and the results appear as follows:

3GPHONER710OLD</SPAN>
40G_EL_TEST_01</SPAN>
40GC-TX-ASSY---</SPAN>
4593FCT-03</SPAN>
45GS05</SPAN>

I then run a macro with variables defined as STRING.
When I compare "40G_EL_TEST_01" against "40GC-TX-ASSY---", it tells me that the first value is larger than the second.

I need a way within the macro to be able to compare the values the same way than EXcel sorted them.

Any suggestions?</SPAN>
</SPAN>

 

[Rick Rothstein]

I then run a macro with variables defined as STRING.
When I compare "40G_EL_TEST_01" against "40GC-TX-ASSY---", it tells me that the first value is larger than the second.
</SPAN>

The reason that "40G_EL_TEST_01" is considered larger than "40GC-TX-ASSY---" is because the first position where they differ is in character position 4... the first text string has an underline character there while the second text string has an upper case C there. If you look at the ASCII values for these two characters, you will see that the underline's value is 95 whereas the upper case C is 67; hence, in a pure text comparison, the first text string in larger than the second because its character at the position where they differ has a higher ASCII value than doesa the character in the same position in the other one.

 

[javelinmania]

But Excel SORT does not sort it that way and must use a different chart. I need a way to make the two match.

 

[Rick Rothstein]

But Excel SORT does not sort it that way and must use a different chart. I need a way to make the two match.

I do not know of any VB code that implement comparisons the way Excel does its sorts. Just so you know how different Excel's rules are, read this article (the
sort rules are listed about half-way down in a section with the subtitle "Check the default sort order rules")...

How to correctly sort alphanumeric data in Excel