software

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 quot;Numberquot;, 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, mtbakerstuTry 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
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: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;mtbakerstuquot; gt; wrote in message oups.com...
gt; Hello,
gt;
gt; I'm struggling with a numerical sort using Excel 2000, though I suspect
gt; this issue is universal to all versions. I want to sort like so:
gt;
gt; 1
gt; 5
gt; 7
gt; 11
gt; 22
gt; 111
gt;
gt; Alas, when I format by quot;Numberquot;, I get this sort result
gt;
gt; 1
gt; 11
gt; 111
gt; 22
gt; 5
gt; 7
gt;
gt; Nothing new here under the sun, but I'm sure there's an easy formula
gt; for getting this to sort in a true numerical order.
gt;
gt; Anyone with any insight into this, please advise.
gt;
gt; Thanks, mtbakerstu
gt;