I'm trying to sort numbers from an outline, so I want to sort it as 1.1, 1.2,
1.3...1.10, 1.11, and so on. Excel sorts them as 1.1, 1.10, 1.11...1.2, 1.3.
I have tried changing the format to several different types, including
Number, Text, Decimal, and cannot get Excel to sort it in the correct order
for an outline.

If you use:

1.01
1.02
1.03

You're life will get much simpler.



Colleen wrote:

I'm trying to sort numbers from an outline, so I want to sort it as 1.1, 1.2,
1.3...1.10, 1.11, and so on. Excel sorts them as 1.1, 1.10, 1.11...1.2, 1.3.
I have tried changing the format to several different types, including
Number, Text, Decimal, and cannot get Excel to sort it in the correct order
for an outline.

--

Dave Peterson

I know that it would be simpler, but I have to cross reference some new
numbers to the old outline numbers, and the old outline did not have the
zeroes after the decimal point. I was hoping that Excel had something that
recognized outlines.

"Dave Peterson" wrote:

If you use:

1.01
1.02
1.03

You're life will get much simpler.



Colleen wrote:

I'm trying to sort numbers from an outline, so I want to sort it as 1.1, 1.2,
1.3...1.10, 1.11, and so on. Excel sorts them as 1.1, 1.10, 1.11...1.2, 1.3.
I have tried changing the format to several different types, including
Number, Text, Decimal, and cannot get Excel to sort it in the correct order
for an outline.

--

Dave Peterson

I think one of the big problems is that excel doesn't see a difference between
the number 1.1 and 1.10.

If your values are text, maybe you could use a helper column, extracting the
numeric value and sort by that:

=--(LEFT(A1,SEARCH(".",A1))&RIGHT("0"&REPLACE(A1,1,SE ARCH(".",A1),""),2))

Seemed to work ok for me.



Colleen wrote:

I know that it would be simpler, but I have to cross reference some new
numbers to the old outline numbers, and the old outline did not have the
zeroes after the decimal point. I was hoping that Excel had something that
recognized outlines.

"Dave Peterson" wrote:

If you use:

1.01
1.02
1.03

You're life will get much simpler.



Colleen wrote:

I'm trying to sort numbers from an outline, so I want to sort it as 1.1, 1.2,
1.3...1.10, 1.11, and so on. Excel sorts them as 1.1, 1.10, 1.11...1.2, 1.3.
I have tried changing the format to several different types, including
Number, Text, Decimal, and cannot get Excel to sort it in the correct order
for an outline.

--

Dave Peterson


--

Dave Peterson

Colleen,
Review of a commercial application by yours truly...
http://www.officeletter.com/blink/specialsort.html
Jim Cone
San Francisco, USA


"Colleen"
wrote in message...
I know that it would be simpler, but I have to cross reference some new
numbers to the old outline numbers, and the old outline did not have the
zeroes after the decimal point. I was hoping that Excel had something that
recognized outlines.

So why not add a new outline as opposed to replacing it. That way you have
the original which references back to the old, and you have the new which
sorts correctly. You can use formulas to create your new structure based on
the old one.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Jim Cone" wrote in message
...
Colleen,
Review of a commercial application by yours truly...
http://www.officeletter.com/blink/specialsort.html
Jim Cone
San Francisco, USA


"Colleen"
wrote in message...
I know that it would be simpler, but I have to cross reference some new
numbers to the old outline numbers, and the old outline did not have the
zeroes after the decimal point. I was hoping that Excel had something
that
recognized outlines.