Struggles with Numercial Sorting. Please Help.
 

Hello,

I'm struggling with a numerical sort using Excel 2000, though I suspect
this issue is universal to all versions. I want to sort like so:

1
5
7
11
22
111

Alas, when I format by "Number", I get this sort result

1
11
111
22
5
7

Nothing new here under the sun, but I'm sure there's an easy formula
for getting this to sort in a true numerical order.

Anyone with any insight into this, please advise.

Thanks, mtbakerstu

Struggles with Numercial Sorting. Please Help.
 

Try to hit Enter or whatever you use to submit one time only.

You have text entries, and formatting text entries as numbers will
not convert to numbers until you reenter.

To effect a reentry.after you have formatted column as number, any of these
1) F2 then Enter, but that is one at a time
2) copy an empty cell (no formula or content), then select the column to convert
Edit, Paste Special, Add
3) use a macro, such as TrimALL or a reentry macro from
http://www.mvps.org/dmcritchie/excel/join.htm
The TrimALL macro also eliminates leading/trailing spaces or
required blanks i.e. CHAR(160).
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"mtbakerstu" wrote in message oups.com...
Hello,

I'm struggling with a numerical sort using Excel 2000, though I suspect
this issue is universal to all versions. I want to sort like so:

1
5
7
11
22
111

Alas, when I format by "Number", I get this sort result

1
11
111
22
5
7

Nothing new here under the sun, but I'm sure there's an easy formula
for getting this to sort in a true numerical order.

Anyone with any insight into this, please advise.

Thanks, mtbakerstu