Excel not sorting numbers correctly - smallest to largest etc.

William_Bila 

What is the point of having excel if it can't do basic math?

Excel can and does do basic math.  It's not perfect, and every once in a while somebody comes up with an actual bug or quirk, but it's not in the realm of basic math. Given the millions using Excel over the years, around the world, the odds that you have discovered a fatal flaw in Excel's abilities with basic math are less than infinitesimal.

And you are coming into a thread that began over  two years ago and may or may not be applicable to your situation.

So why don't we back up a bit and get a more complete description from you of what you're working with and what is and is not working. And, if it's possible, without violating confidentiality or proprietary data restrictions, it would help us help you if you could post a copy of your actual workbook on OneDrive or GoogleDrive, with a link pasted here that grants edit access.

SergeiBaklan 

Any other ideas? Neither =clean(A2) nor =trim(A2) work in my case.

My data is not from the web, is a result of a INDEX MATCH horizontal lookup. 

SergeiBaklan  I ended up googling the formula =CLEAN(A2) to strip nonprintable characters from cell text and was able to get that to work in this situation.  Rather cumbersome but at least I had success and will be able to continue using that to help me. I'm going include my notes that I wrote that worked for me in case anyone else can find this of benefit to them:

Issue:  Exporting to Excel from another database may have created characters that are hidden which impacts the ability to sort columns correctly.  (Simply changing the cell format from text or general or to number format will not correct the problem) This can be fixed using the CLEAN formula below:

=CLEAN(A2) Strip non-printable characters from the cell.  Non-printable characters constitute the first 32 characters of the 7-bit ASCI code, which houses the 128 text characters used by all computers for data storage and computing. Because 7-bit ASCI code is widely used in computer processing, copying and pasting data from other applications or business databases frequently copies the nonprintable characters too. To remove nonprintable characters:

As an example:

To clean A2 through A25:  (assuming row 1 is the title field)

1. Open your spreadsheet and right-click the column letter located to the right of the data column you want to change.
   1. select "Insert" to create a new "B" column
   2. Click cell B2 (assuming B1 is the title fields)
   3. Press "Shift" key and click cell B25
   4. Type =CLEAN(A2) then press "Ctrl-Enter"
2. To now clean up the spreadsheet and remove the created column:
1. Select & Copy the data from the newly created column (B)
2. Paste Special the “Value” into the cleaned column (A)
3. Delete the newly created column (B)
4. Click the info icon if there is a green box in the left of the cell and choose “convert to a number” for those that have it.  If none, do nothing.
5. Re-sort the column and verify that the sort worked correctly.
   
   This HELPED me resolve my issue - I hope it helps another too!

If they are not generated within Excel but copy/pasted from the Web, most probably you have non-printable characters inside your cells with numbers. That doesn't matter you used same site or not.

You may check by LEN() applied to cells if all of them returns 4.

If not you may apply =CLEAN(A1)*1 to such cells to remove non-printable characters.