Sort Outline numbers

[SpaceCamel]

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,

 

[Paul Cordts]

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.

 

[JW]

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-

 

[JW]

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.

 

[SpaceCamel]

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,

 

[Jim Cone]

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"
<[email protected]>
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,