sort issue: Excel seems to be treating my column of numbers"alphabetically"

  • Thread starter adeviantsubcultureof1
  • Start date
A

adeviantsubcultureof1

Hi All,

I am trying to sort a column of numbers in numerical order. When I
attempt to do this excel seems to want to sort them as though they
were letters. Here is an example:
1 
10032 
10044 
10071 
10107 
10175 
102164 
10306 
10353 
10394 
10421 
1044 
1045 
10471 
10670 
10683 
10730 
10759 
10769 
10795 
1081 
10853 
10875 
10877 
110800 
11137 
11246 
113366 
11385 
1142 
11480 
11524 
11563 
1165 
117 
12062 

I have checked that it is formatted as a number and not text. I have
no idea how to make excel sort them in numerical order.

Any thoughts?

Cheers,

D
 
J

Jim Cone

If the numbers are not aligned to the right side of the column then
Excel sees them as text. Text aligns left.
You can copy the value of 1 from a cell and then use Paste Special to
multiply your data by that value. That will convert your text numbers to numbers.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


<[email protected]>
wrote in message
Hi All,
I am trying to sort a column of numbers in numerical order. When I
attempt to do this excel seems to want to sort them as though they
were letters. Here is an example:
1
10032
10044
10071
10107
10175
102164
10306
10353
10394
10421
1044
1045
10471
10670
10683
10730
10759
10769
10795
1081
10853
10875
10877
110800
11137
11246
113366
11385
1142
11480
11524
11563
1165
117
12062

I have checked that it is formatted as a number and not text. I have
no idea how to make excel sort them in numerical order.
Any thoughts?
Cheers,
D
 
J

JLGWhiz

Using the CLng function worked for me. Converts from string to number.

Sub srtrng()
Dim c As Range
For Each c In Sheets(1).Range("A1:A35")
c.Value = CLng(c.Value)
Next
Range("A1:A35").Sort Key1:=Range("A1"), Order1:=xlAscending
End Sub
 
T

Tim Zych

Another way to convert them:

With ActiveSheet.UsedRange
.Value = .Value
End With

--
Tim Zych
SF, CA

Hi All,

I am trying to sort a column of numbers in numerical order. When I
attempt to do this excel seems to want to sort them as though they
were letters. Here is an example:
1
10032
10044
10071
10107
10175
102164
10306
10353
10394
10421
1044
1045
10471
10670
10683
10730
10759
10769
10795
1081
10853
10875
10877
110800
11137
11246
113366
11385
1142
11480
11524
11563
1165
117
12062

I have checked that it is formatted as a number and not text. I have
no idea how to make excel sort them in numerical order.

Any thoughts?

Cheers,

D
 
Top