How do I sort so that 1.2 comes before 1.10?

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,SEARCH(".",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" <Colleen@discussions.microsoft.com>
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" <jim.coneXXX@rcn.comXXX> wrote in message
news:eyhIIghOGHA.3100@tk2msftngp13.phx.gbl...
> Colleen,
> Review of a commercial application by yours truly...
> http://www.officeletter.com/blink/specialsort.html
> Jim Cone
> San Francisco, USA
>
>
> "Colleen" <Colleen@discussions.microsoft.com>
> 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.
>
>

Originally Posted by 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.



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

This seems to be the solution I need as well, but the only problem is, it no longer works when I use numbers with decimals higher than 2 digits. Such as 1.1, 1.2, 1.3, then 1.01, 1.02, 1.03, and 1.103, 1.104, 1.105, etc.

I tried manipulating the code a bit to make that work, but it doesn't seem to put them in the right order, with your formula, it seems to drop the first decimal digit, thus turning a 98.41 and a 98.141 as examples both into 98.41 with this formula.

Any help would be outstanding.