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

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

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

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)



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

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

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

" wrote:

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

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

Another way to convert them:

With ActiveSheet.UsedRange
.Value = .Value
End With

--
Tim Zych
SF, CA

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

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

Hi Tim, I still get string value using the .Value = .Value.

"Tim Zych" wrote:

Another way to convert them:

With ActiveSheet.UsedRange
.Value = .Value
End With

--
Tim Zych
SF, CA

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