|
sorting numbers more than one decimal
I have pasted an example of the numbers below. Why does the sorting start
over at the bottom of the list? I want the last number on the list 87200.028.1 to appear after the fourth number 87200.028 and then 87200.029 would be after 87200.028.1 I realize that this is directly related to the faxt that the first numbers have one decimal and the ones at the bottom of the list have two decimals. What can I do about it? 87200.022 87200.023 87200.027 87200.028 87200.029 87200.031 87200.031 87200.032 87200.032 87200.033 87200.034 87200.035 87200.035 87200.036 87200.037 85600.403.1 86100.708.1 86500.403.2 87200.028.1 |
I'd use an extra column and convert all the numbers to text (and no change to
the text values). If your data is A1:Axx, then insert a new column B and =a1&"" drag down And sort your range based on column B. Erin wrote: I have pasted an example of the numbers below. Why does the sorting start over at the bottom of the list? I want the last number on the list 87200.028.1 to appear after the fourth number 87200.028 and then 87200.029 would be after 87200.028.1 I realize that this is directly related to the faxt that the first numbers have one decimal and the ones at the bottom of the list have two decimals. What can I do about it? 87200.022 87200.023 87200.027 87200.028 87200.029 87200.031 87200.031 87200.032 87200.032 87200.033 87200.034 87200.035 87200.035 87200.036 87200.037 85600.403.1 86100.708.1 86500.403.2 87200.028.1 -- Dave Peterson |
It starts over because the values with a single decimal point are stored as
numbers; those with 2 decimal points are stored as text. Excel sorts numbers before text. You have to conver all of the "numbers" to text. On Tue, 8 Mar 2005 12:55:06 -0800, "Erin" wrote: I have pasted an example of the numbers below. Why does the sorting start over at the bottom of the list? I want the last number on the list 87200.028.1 to appear after the fourth number 87200.028 and then 87200.029 would be after 87200.028.1 I realize that this is directly related to the faxt that the first numbers have one decimal and the ones at the bottom of the list have two decimals. What can I do about it? 87200.022 87200.023 87200.027 87200.028 87200.029 87200.031 87200.031 87200.032 87200.032 87200.033 87200.034 87200.035 87200.035 87200.036 87200.037 85600.403.1 86100.708.1 86500.403.2 87200.028.1 |
Daves example worked perfectly!
Myrna, Thank you for the information that does explain WHY. However, simply formatting the cells as text and sorting didn't work. I had tried that before posting. It does make me curious as to why formatting as text didn't get the desired results. But, anyhow Thank You! Everyone "Erin" wrote: I have pasted an example of the numbers below. Why does the sorting start over at the bottom of the list? I want the last number on the list 87200.028.1 to appear after the fourth number 87200.028 and then 87200.029 would be after 87200.028.1 I realize that this is directly related to the faxt that the first numbers have one decimal and the ones at the bottom of the list have two decimals. What can I do about it? 87200.022 87200.023 87200.027 87200.028 87200.029 87200.031 87200.031 87200.032 87200.032 87200.033 87200.034 87200.035 87200.035 87200.036 87200.037 85600.403.1 86100.708.1 86500.403.2 87200.028.1 |
Formatting a cell that contains a number doesn't change the value from number to
text. But if you make a change (simply hitting F2|Enter is enough) will change it, though. If you put =isnumber(a1) with 123 in a1, you can fiddle with the format and see when/if it changes. Erin wrote: Daves example worked perfectly! Myrna, Thank you for the information that does explain WHY. However, simply formatting the cells as text and sorting didn't work. I had tried that before posting. It does make me curious as to why formatting as text didn't get the desired results. But, anyhow Thank You! Everyone "Erin" wrote: I have pasted an example of the numbers below. Why does the sorting start over at the bottom of the list? I want the last number on the list 87200.028.1 to appear after the fourth number 87200.028 and then 87200.029 would be after 87200.028.1 I realize that this is directly related to the faxt that the first numbers have one decimal and the ones at the bottom of the list have two decimals. What can I do about it? 87200.022 87200.023 87200.027 87200.028 87200.029 87200.031 87200.031 87200.032 87200.032 87200.033 87200.034 87200.035 87200.035 87200.036 87200.037 85600.403.1 86100.708.1 86500.403.2 87200.028.1 -- Dave Peterson |
| All times are GMT +1. The time now is 12:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com