Excel Numeric Sort

[rnordquest]

How do I get Excel to do a numeric sort? Data example:

10
40
20
110
100

Standard sort gives me
10
100
110
20
40

What I want is:
10
20
40
100
110

How do I get it?

Thanks,
Roger

 

[Beggar]

The problem is that your data is formatted as text rather than as numbers - or at least it was when you entered it.

Even when you change the formatting back to a number, for some reason it still can be interpreted as text even though the formatting says it's a number

What I've done is use the function VALUE() in adjacent cells and then do a Copy -> Paste Special -> Values to force them to be numbers again.

Hopefully somebody has a better answer than this but it's worked for my limited needs.

 

[palusa]

Roger,
the sort you have shown results from text sorting: it looks like as your numbers are treated as strings, not values.
To fix:
(a) remove any quote in front of your values having them preceeded by a sign + (i.e. BAD: '10 --> GOOD: +10)
(b) sort them again.

Hopefully it will work.

_LF

 

[rnordquest]

There's no quote in front. The first thing I did was try to reformat the column to numeric. I'm going to try to copy/paste special/values.

 

[MintJulep]

Some place there is a check box that turns on an option to

"Treat text that looks like a number as a number."

 

[IRstuff]

Sometimes, in Excel, particularly with imported numbers, is that there are remnant text artifacts that force the formatting to remain text.

A simple check is to reenter any of the numbers manually. If the justification changes from left to right, then you'll know that the column is still being treated as text.

A simple solution for short columns is to simply select the number as if to edit and just hit the enter key. That will usually fix the formatting back to numeric.




TTFN

 

[tmukhtar]

A quick manual method would be to select the cells, change the format by Format --> Cells and then repeatedly pressing the keys of F2 and {ENTER}, alternately, untill done. Here F2 is keyboard shortcut to enter edit mode of cell and {ENTER} key is to go to next cell.

To avoid the lobor of pressing the keys, following macro can also be executed, after changing the format while the cells remain selected

[COLOR=red yellow]Sub ApplyFormat()
Dim MyCell As Range
For Each MyCell In Selection.Cells
MyCell.Formula = MyCell.Formula
Next
End Sub
[/color]

 

[onlyadrafter]

Hello,

A quicker way to get the data to be number is to enter 1 in a cell somewhere, select this cell, select EDIT --> COPY, select the cells with the data in, select EDIT --> PASTE SPECIAL and check the MULTIPLY BOX. The data should now be numberic, now do your sort.

----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!