Forum Discussion

Copper Contributor

Jun 23, 2018

Excel not sorting numbers correctly - smallest to largest etc.

I have been helping a friend organize a excel file with 4 digit numbers. These numbers are in the 6000 to 8000 range. When I filter or sort the numbers from smallest to largest there are numbers , ...

excel

Jamie Collins

Copper Contributor

Jun 24, 2018

They are copy and pasted all from the same site. Chunks of the numbers will sort correctly but then further down they stop sorting right.

SergeiBaklan

Diamond Contributor

Jun 24, 2018

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.

Monty_G
Copper Contributor
May 20, 2019
SergeiBaklan, can you explain in detail how to do this, how do I apply this, where do i type it, find it, etc.
DLans
Copper Contributor
Apr 15, 2019
SergeiBaklan Thank you, the =CLEAN function helped me out immensely. I cannot see any difference between the input and the output, but the output I can sort properly. Thanks again!
- SergeiBaklan
  Diamond Contributor
  Apr 15, 2019
  DLans , so far so good, you are welcome. You could see the difference if check =LEN(A1) and =LEN(CLEAN(A1)) if in A1 is initial value