×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Numeric Sort

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

RE: Excel Numeric Sort

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.

RE: Excel Numeric Sort

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

RE: Excel Numeric Sort

(OP)
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.

RE: Excel Numeric Sort

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

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

RE: Excel Numeric Sort

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

RE: Excel Numeric Sort

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

Sub ApplyFormat()
Dim MyCell As Range
For Each MyCell In Selection.Cells
    MyCell.Formula = MyCell.Formula
Next
End Sub

RE: Excel Numeric Sort

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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources