Text & Number Formats

[Gator]

When I open a new blank spreadsheet, the default format is General. I
highlight a field (A) and right click to change format to Text. If I enter
numeric data in the cells, how can I sort them as if they were in the number
format? The reason I ask this is because many numbers have 0 to begin with.
But, sometimes I want to see them in numeric order.

 

[Gator]

Another thing, the numbers vary in the number of digits. So, if I have a
number that is 1 and another that is 02, the field sorts them as 02 first and
1 listed after 02. I want to see the sort in numeric terms, where 1 is
listed first, but want to keep the field in text format.

 

[Dave Peterson]

If you're using xl2002+ (I think it was added in xl2002), you'll get an option
to sort text numbers as number numbers -- but only if you go through the
Data|Sort (xl2003 menu system).

If you're using xl2k or below, you could use a helper column and convert all the
number looking entries to real numbers.

=if(isnumber(-a1),--a1,a1)

Then fill the column with this kind of formula and sort by that.