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,

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,

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,

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,

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,

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,