Forum Discussion

Jamie Collins's avatar
Jamie Collins
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
nullnull's avatar
nullnull
Copper Contributor
Jan 06, 2021

I made an account just to let anybody Googling this know...

 

My numbers had spaces after them, darn things! =ISNUMBER could tell they weren't "numbers" even though I'd set the cell format to Number. =CLEAN didn't work; paste special > values didn't work.

 

What worked was.... =TRIM! (I only thought of this because I just used Data > Trim Whitespace in Google Sheets last week.)

 

So if you paste special (as values) your data from the web, and/or clean them, and they still won't sort, try:

 

=TRIM(A1)

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 06, 2021

    nullnull 

    Just in case, CLEAN() removes characters with ASCII codes from 0 to 31, TRIM() removes space (code 32). But no one of them removes non-breaking space (code 160) which is the most often case when copy/paste from web. 

    • JRVM1's avatar
      JRVM1
      Copper Contributor
      Apr 14, 2021

      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. 

      • Philip_Hinton's avatar
        Philip_Hinton
        Copper Contributor
        May 31, 2022
        I realise it is now a year later, but for posterity try:
        =SUBSTITUTE(A2,CHAR(160),"")

        You can then wrap that in CLEAN and TRIM:
        =CLEAN(TRIM(SUBSTITUTE(A2,CHAR(160),"")))

        You'll occasionally find other white-space characters (e.g. "hairspace") and there are web pages that list these with their ASCII codes.

Resources