Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Outline numbers
I need to sort a column of outline numbers (WBS structure numbers).
Like : 1.0 1.0.1 1.3.2.1 3.10.2.1 2.0.2 Excel treats some as text and some as numners. Is the a function that can do that? Can it be done without using a macro? Thanks, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Outline numbers
If you force all cells to be text format, you should be able to get your
sort. From the Format | Cells menu select Text on the number tab. -- Paul Cordts "SpaceCamel" wrote: I need to sort a column of outline numbers (WBS structure numbers). Like : 1.0 1.0.1 1.3.2.1 3.10.2.1 2.0.2 Excel treats some as text and some as numners. Is the a function that can do that? Can it be done without using a macro? Thanks, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Outline numbers
First, you need to make sure that your column is formatted as text.
That way, it will allow the column to actually hold a value such as 1.0 without changing it automatically to just 1. Now, that alone will not cause your sort to be correct. What I did was stick in a helper column and use a SUBSTITUTE formula to replace the decimal with an underscore. I then sorted on that column and came up with the correct result. The formula used is: =SUBSTITUTE(A2,".","_") Using your data, the result when sorting on the column containing the above formula resulted in: 1.0 1.0.1 1.3.2.1 2.0.2 3.10.2.1 HTH -Jeff- SpaceCamel wrote: I need to sort a column of outline numbers (WBS structure numbers). Like : 1.0 1.0.1 1.3.2.1 3.10.2.1 2.0.2 Excel treats some as text and some as numners. Is the a function that can do that? Can it be done without using a macro? Thanks, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Outline numbers
Paul, it would make sense if it worked like that. Unfortunately, just
changing the column to text still results in an incorrect sort. See the post above for a solution. Paul Cordts wrote: If you force all cells to be text format, you should be able to get your sort. From the Format | Cells menu select Text on the number tab. -- Paul Cordts "SpaceCamel" wrote: I need to sort a column of outline numbers (WBS structure numbers). Like : 1.0 1.0.1 1.3.2.1 3.10.2.1 2.0.2 Excel treats some as text and some as numners. Is the a function that can do that? Can it be done without using a macro? Thanks, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Outline numbers
When I try the substitution on this list the sort still is not correct.
3.10 3_10 3.11 3_11 3.6 3_6 3.7 3_7 10 comes before 6, etc. I got a VBA funtion to work but was hoping for a technique that didn't require the addition of another column. Thanks, "JW" wrote: First, you need to make sure that your column is formatted as text. That way, it will allow the column to actually hold a value such as 1.0 without changing it automatically to just 1. Now, that alone will not cause your sort to be correct. What I did was stick in a helper column and use a SUBSTITUTE formula to replace the decimal with an underscore. I then sorted on that column and came up with the correct result. The formula used is: =SUBSTITUTE(A2,".","_") Using your data, the result when sorting on the column containing the above formula resulted in: 1.0 1.0.1 1.3.2.1 2.0.2 3.10.2.1 HTH -Jeff- SpaceCamel wrote: I need to sort a column of outline numbers (WBS structure numbers). Like : 1.0 1.0.1 1.3.2.1 3.10.2.1 2.0.2 Excel treats some as text and some as numners. Is the a function that can do that? Can it be done without using a macro? Thanks, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Outline numbers
Using Text to Columns, with the "." as the separator will work, but that requires several columns. If you are desperate<g, my commercial add-in "Special Sort" will do it. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "SpaceCamel" wrote in message When I try the substitution on this list the sort still is not correct. 3.10 3_10 3.11 3_11 3.6 3_6 3.7 3_7 10 comes before 6, etc. I got a VBA funtion to work but was hoping for a technique that didn't require the addition of another column. Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort numbers | Excel Discussion (Misc queries) | |||
Sort "text" numbers with and without leading zeros as numbers | Excel Discussion (Misc queries) | |||
sort an outline without changing the hierarchy | Excel Discussion (Misc queries) | |||
Sort by last two numbers | Excel Discussion (Misc queries) | |||
Help Sorting Outline Numbers | Excel Discussion (Misc queries) |