odd sort issue with numbers

[Lilivati]

I am trying to sort a column of data on my worksheet that contains a
bunch of large numbers. When I did this programically, I noticed that
while it was sorting "classes" of numbers correctly, the overall sort
was not correct.

What I mean by this is I have a bunch of numbers, some of which begin
with 65, others that begin with 23 (plus more types, but this will
serve for an example). 23069000 is clearly greater than 65116, but
when I sort it in ascending order 23069000 is above 65116 in the list.
However, 65115 will be above 65116, and 23069000 will be above
23069001. So I get something like this:

23069000
23069001
65115
65116

(What I want is something like this:

65115
65116
23069000
23069001

i.e. sorted in proper numeric order)

I figured something was amiss with my code, so I tried sorting using
the data->sort menu, and much to my surprise got the same result, so
the problem is not my code, but something I'm not seeing in the sort
process.

This column is formated as a number (rather than general or text).

Here is my code snippet in case it should help:

Sub sort()

With Worksheets(1)
.Range("A:F").sort _
Key1:=Range("C2"), _
Order1:=xlAscending, _
Key2:=Range("A2"), _
Order2:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With

End Sub

(I also tried sorting with just the first range (C2), which has the
numbers mentioned above, but it made no difference in the result.)

As always, any help is very much appreciated.

 

[Tom Ogilvy]

assume one of the troublesome numbers is in cell c1. In another cell put in

=isnumber(c1)

My guess is it will return false and your numbers are stored as text (which
is the way they are being sorted).

 

[Gord Dibben]

Your numbers are text,

Formatting to number is not enough.

Format to General or number then copy an empty cell and select your data.

Paste Special>Add>OK>Esc


Gord Dibben MS Excel MVP