case matters when sorting strings??

[Barq]

Hi,
I have a general question:
I was sorting some strings using IF statements i.e. If string1 > string2 etc...
I noticed that the list that I end up with has puts "a" after "Z".

Why does this happen? Is there a way to make it so that the end result will be in the order of "A", "a", "B", "b" .. etc?

I can't convert the strings to all upper case or proper case because these are company names that sometime have unique ways of writing their names, e.g. eBay Inc, so I want to preserve that formatting.

Thanks!

 

[Norie]

You don't need to convert the actual strings, just use UCase (or Lcase) to compare them.

If UCase(string1)>UCase(string2) Then

You could also place this at the top of the module.

Option Compare Text

 

[Barq]

Thanks!

 

[Richard Schollar]

Hi Barq

In answer to your question of why it happens, it is because of the ASCII codes assigned to each character ('A' = 65, 'B' = 66, ...., 'a' = 97, 'b' = 98 and so on) so when Excel is sorting and it sees two characters of A ('A' and 'a') it will sort 'A' before 'a' as 65 is less than 97 (sort ascending).

Hope this helps!

Richard

 

[Barq]

ah, I see... Thanks!