 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / December 2007sort 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 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.  SignatureJim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) <adeviantsubcultureof1@gmail.com> 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 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 > Hi All, > [quoted text clipped - 46 lines] > > D Another way to convert them: With ActiveSheet.UsedRange .Value = .Value End With SignatureTim 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 Hi Tim, I still get string value using the .Value = .Value. > Another way to convert them: > [quoted text clipped - 52 lines] > > D |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.