How do I sort so that 1.2 comes before 1.10?

[Colleen]

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]

If you use:

1.01
1.02
1.03

You're life will get much simpler.

 

[Colleen]

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]

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,SEARCH(".",A1),""),2))

Seemed to work ok for me.

 

[Jim Cone]

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


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

 

[Ken Wright]

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
------------------------------­------------------------------­----------------