| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sort 2 column in the same time "" as dictionary "" | Excel Worksheet Functions | |||
| Sort "text" numbers with and without leading zeros as numbers | Excel Discussion (Misc queries) | |||
| My Column display as "numbers" instead of "alphabets" | Excel Discussion (Misc queries) | |||
| Can Excel "extract" ranges of numbers listed in the A column? | Excel Programming | |||
| Can Excel "extract" ranges of numbers listed in the A column? | Excel Discussion (Misc queries) | |||
Linear Mode
