Excel Numeric Sort
Excel Numeric Sort
(OP)
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
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





RE: Excel Numeric Sort
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.
RE: Excel Numeric Sort
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
RE: Excel Numeric Sort
RE: Excel Numeric Sort
"Treat text that looks like a number as a number."
RE: Excel Numeric Sort
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
RE: Excel Numeric Sort
To avoid the lobor of pressing the keys, following macro can also be executed, after changing the format while the cells remain selected
Sub ApplyFormat()
Dim MyCell As Range
For Each MyCell In Selection.Cells
MyCell.Formula = MyCell.Formula
Next
End Sub
RE: Excel Numeric Sort
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!