Share via

Column of numbers not sorting into numerical order. Formating issue?

Anonymous
2011-04-14T22:36:12+00:00

I have several columns of information the first of which, column A, is a range of item #'s.  They are in a random order and need to be numerical order while still keeping the row information together.   When I try sorting or filtering into numerical order (1., 2., 3., 4.,  ... etc) both commands are putting them in this order:  1., 10., 101., 102.,103.,104.,105.,106.,107.,108.,109.,11.,110., and so on.   I have not been able to determine what the problem is . 

I have tried reformatting the entire column as text and as number but to no avail.  What am I doing wrong?  this info was on this spreadsheet came from a report created on our system at work and exported to Excel.  Is that the problem?  Do I just need to go in and re-enter the numbers myself?  (seems like such an labor intensive thing to do.)  Please advise or suggest a different way to get what I need.  FYI: the system we use at work is considerably user UNfriendly, therefore being able to export what little useable information we can is more efficient than creating the spreadsheet from scratch... although I'm beginning to wonder at this point if my time would've been better used doing just that!

Thank you for any assistance provided!!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2011-04-14T22:42:02+00:00

    If you want those values to be text...which they already are...then you are getting text-sorting.

    If you want them to be numbers....try this:

    • Select the range of "numbers"

    • Data.Text-to-Columns...Click: Finish

    Now the values will sort numerically.

    If they don't...there's something else going on with the data. Edit a cell and see if it ONLY contains what you think it contains.

    Does that help?

    100+ people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-04-15T00:31:52+00:00

    Another thing you might try is inserting a blank column before Column A (this is the new Column A) and adding the formula =B2*1 (assuming Row 1 has column headers in it) in Cell A2, then fill down.  This will guarantee that all of your numbers are formatted as numbers.  If Row 1 has column headers, don't forget to add a header to the new Column A.  Then try sorting by Column A again.  Good luck.

    10+ people found this answer helpful.
    0 comments
  2. Anonymous
    2011-04-14T23:16:20+00:00

    Copy a blank cell (this will be treated as zero). Seelct all the proble 'numbers' and use Paste Special > Add. When you perform some arithmetic, Excel generally converts 'text-numbers' to real numbers. best wishes

    6 people found this answer helpful.
    0 comments
  3. Anonymous
    2011-04-15T00:00:15+00:00

    thank you I will try this as well!  I appreciate the assistance!

    2 people found this answer helpful.
    0 comments
  4. Anonymous
    2011-04-14T23:57:00+00:00

    Yes, there was a delimiter (the comma) but managed to work my way through the text to column wizard after a few tries!  Thank you for your help!

    1 person found this answer helpful.
    0 comments