| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| all the cells in excell put 2 decimal points in all numbers no ma. | Excel Discussion (Misc queries) | |||
| sorting names alphabetically when names may start with numbers | Excel Discussion (Misc queries) | |||
| Entering numbers with variable decimal places. | Excel Worksheet Functions | |||
| Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
| Sorting imported "numbers" | Excel Discussion (Misc queries) | |||
Linear Mode
