Home

Excel not sorting numbers correctly - smallest to largest etc.

%3CLINGO-SUB%20id%3D%22lingo-sub-207258%22%20slang%3D%22en-US%22%3EExcel%20not%20sorting%20numbers%20correctly%20-%20smallest%20to%20largest%20etc.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207258%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20been%20helping%20a%20friend%20organize%20a%20excel%20file%20with%204%20digit%20numbers.%26nbsp%3B%20These%20numbers%20are%20in%20the%206000%20to%208000%20range.%26nbsp%3B%20When%20I%20filter%20or%20sort%20the%20numbers%20from%20smallest%20to%20largest%20there%20are%20numbers%20%2C%20in%20the%206200%20to%206800%20range%2C%20missing%20where%20they%20should%20be%20and%20then%20excel%20places%20them%20in%20order%20farther%20down%20in%20the%207000%20to%208000%20range.%26nbsp%3B%20I%20am%20sure%20there%20is%20an%20answer%20and%20I%20did%20find%20that%20version%2016.9%20and%20above%20have%20issues.%26nbsp%3B%20Is%20anyone%20else%20having%20this%20issue%20and%20if%20so%2C%20what%20can%20be%20done%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-207258%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207319%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20not%20sorting%20numbers%20correctly%20-%20smallest%20to%20largest%20etc.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207319%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20they%20are%20not%20generated%20within%20Excel%20but%20copy%2Fpasted%20from%20the%20Web%2C%20most%20probably%20you%20have%20non-printable%20characters%20inside%20your%20cells%20with%20numbers.%20That%20doesn't%20matter%20you%20used%20same%20site%20or%20not.%3C%2FP%3E%0A%3CP%3EYou%20may%20check%20by%20LEN()%20applied%20to%20cells%20if%20all%20of%20them%20returns%204.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%26nbsp%3Bnot%20you%20may%20apply%20%3DCLEAN(A1)*1%20to%20such%20cells%20to%20remove%20non-printable%20characters.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207275%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20not%20sorting%20numbers%20correctly%20-%20smallest%20to%20largest%20etc.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207275%22%20slang%3D%22en-US%22%3EThey%20are%20copy%20and%20pasted%20all%20from%20the%20same%20site.%20Chunks%20of%20the%20numbers%20will%20sort%20correctly%20but%20then%20further%20down%20they%20stop%20sorting%20right.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207260%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20not%20sorting%20numbers%20correctly%20-%20smallest%20to%20largest%20etc.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207260%22%20slang%3D%22en-US%22%3E%3CP%3ENope%2C%20in%20my%20case%20it%20works%20as%20expected.%20Are%20your%20numbers%20are%20integers%20in%20Excel%2C%20or%20that%20texts%3F%20Did%20you%20generate%20them%20manually%20(e.g.%20fill%20series)%20or%20copy%2Fpasted%20from%20Web%20for%20example%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-454381%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20not%20sorting%20numbers%20correctly%20-%20smallest%20to%20largest%20etc.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-454381%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%2C%20the%20%3DCLEAN%20function%20helped%20me%20out%20immensely.%20I%20cannot%20see%20any%20difference%20between%20the%20input%20and%20the%20output%2C%20but%20the%20output%20I%20can%20sort%20properly.%20Thanks%20again!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-455959%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20not%20sorting%20numbers%20correctly%20-%20smallest%20to%20largest%20etc.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-455959%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F321331%22%20target%3D%22_blank%22%3E%40DLans%3C%2FA%3E%26nbsp%3B%2C%20so%20far%20so%20good%2C%20you%20are%20welcome.%20You%20could%20see%20the%20difference%20if%20check%20%3DLEN(A1)%20and%20%3DLEN(CLEAN(A1))%20if%20in%20A1%20is%20initial%20value%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-615348%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20not%20sorting%20numbers%20correctly%20-%20smallest%20to%20largest%20etc.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-615348%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%20can%20you%20explain%20in%20detail%20how%20to%20do%20this%2C%20how%20do%20I%20apply%20this%2C%20where%20do%20i%20type%20it%2C%20find%20it%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-678090%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20not%20sorting%20numbers%20correctly%20-%20smallest%20to%20largest%20etc.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-678090%22%20slang%3D%22en-US%22%3E%3CP%3EThese%20replies%20are%20not%20helping%20me%20troubleshoot%20my%20issue%20-%26nbsp%3B%3CBR%20%2F%3EI%20have%20no%20hidden%20columns%2C%20no%20formulas%2C%20nothing%20but%20raw%20data%20in%20my%20spreadsheet%20-%20yet%20when%20I%20sort%20from%20small%20to%20large%20or%20vice%20versa%20with%20cell%20formatting%20in%20%22number%22%20format%2C%20it%20sorts%20the%20majority%20correctly%20then%20puts%20several%20at%20the%20bottom%20that%20are%20out%20of%20sequence.%26nbsp%3B%20I've%20copied%20the%20cell%20format%20of%20one%20that%20sorts%20correctly%20and%20pasted%20to%20those%20that%20didn't%2C%20then%20resorted%20to%20see%20if%20it%20worked%20but%20it%20doesn't.%26nbsp%3B%20I%20don't%20know%20what%20to%20do%20to%20fix%20this%20major%20annoying%20problem%20-%20any%20suggestions%20are%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-678110%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20not%20sorting%20numbers%20correctly%20-%20smallest%20to%20largest%20etc.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-678110%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356677%22%20target%3D%22_blank%22%3E%40KerrilynnAZ%3C%2FA%3E%26nbsp%3B%2C%20if%20in%20any%20empty%20cell%20you%20try%20%3DISNUMBER()%20on%20one%20the%20cells%20which%20are%20in%20the%20bottom%2C%20what%20will%20be%20the%20result%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-678332%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20not%20sorting%20numbers%20correctly%20-%20smallest%20to%20largest%20etc.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-678332%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20I%20ended%20up%20googling%20the%20formula%26nbsp%3B%3DCLEAN(A2)%20to%20strip%20nonprintable%20characters%20from%20cell%20text%20and%20was%20able%20to%20get%20that%20to%20work%20in%20this%20situation.%26nbsp%3B%20Rather%20cumbersome%20but%20at%20least%20I%20had%20success%20and%20will%20be%20able%20to%20continue%20using%20that%20to%20help%20me.%20I'm%20going%26nbsp%3Binclude%20my%26nbsp%3Bnotes%20that%26nbsp%3BI%20wrote%26nbsp%3Bthat%20worked%20for%20me%20in%20case%20anyone%20else%20can%20find%20this%20of%20benefit%20to%20them%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CU%3EIssue%3C%2FU%3E%3C%2FSTRONG%3E%3A%26nbsp%3B%20Exporting%20to%20Excel%20from%20another%20database%20may%20have%20created%26nbsp%3Bcharacters%20that%20are%20hidden%20which%20impacts%20the%20ability%20to%20sort%20columns%20correctly.%20%26nbsp%3B(Simply%20changing%20the%20cell%20format%20from%20text%20or%20general%20or%26nbsp%3Bto%20number%20format%20will%20not%20correct%20the%20problem)%20This%20can%20be%20fixed%20using%20the%20CLEAN%20formula%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCLEAN(A2)%26nbsp%3BStrip%20non-printable%20characters%20from%20the%20cell.%26nbsp%3B%20Non-printable%20characters%20constitute%20the%20first%2032%20characters%20of%20the%207-bit%20ASCI%20code%2C%20which%20houses%20the%20128%20text%20characters%20used%20by%20all%20computers%20for%20data%20storage%20and%20computing.%20Because%207-bit%20ASCI%20code%20is%20widely%20used%20in%20computer%20processing%2C%20copying%20and%20pasting%20data%20from%20other%20applications%20or%20business%20databases%20frequently%20copies%20the%20nonprintable%20characters%20too.%20To%20remove%20nonprintable%20characters%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EAs%20an%20example%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ETo%20clean%20A2%20through%20A25%3A%26nbsp%3B%20(assuming%20row%201%20is%20the%20title%20field)%3C%2FP%3E%3COL%3E%3CLI%3EOpen%20your%20spreadsheet%20and%20right-click%20the%20column%20letter%20located%20to%20the%20right%20of%20the%20data%20column%20you%20want%20to%20change.%3COL%3E%3CLI%3Eselect%20%22Insert%22%20to%20create%20a%20new%20%22B%22%20column%3C%2FLI%3E%3CLI%3EClick%20cell%26nbsp%3BB2%20(assuming%20B1%20is%20the%20title%20fields)%3C%2FLI%3E%3CLI%3EPress%20%22Shift%22%20key%20and%20click%20cell%26nbsp%3BB25%3C%2FLI%3E%3CLI%3EType%20%3DCLEAN(A2)%20then%20press%20%22Ctrl-Enter%22%3C%2FLI%3E%3C%2FOL%3E%3C%2FLI%3E%3CLI%3ETo%20now%20clean%20up%20the%20spreadsheet%20and%20remove%20the%20created%20column%3A%3C%2FLI%3E%3COL%3E%3CLI%3ESelect%20%26amp%3B%20Copy%20the%20data%20from%20the%20newly%20created%20column%20(B)%3C%2FLI%3E%3CLI%3EPaste%20Special%20the%20%E2%80%9CValue%E2%80%9D%20into%20the%20cleaned%20column%20(A)%3C%2FLI%3E%3CLI%3EDelete%20the%20newly%20created%20column%20(B)%3C%2FLI%3E%3CLI%3EClick%20the%20info%20icon%26nbsp%3Bif%20there%20is%20a%20green%20box%20in%20the%20left%20of%20the%20cell%26nbsp%3Band%20choose%20%E2%80%9Cconvert%20to%20a%20number%E2%80%9D%20for%20those%20that%20have%20it.%26nbsp%3B%20If%20none%2C%20do%20nothing.%3C%2FLI%3E%3CLI%3ERe-sort%20the%20column%20and%20verify%20that%20the%20sort%20worked%20correctly.%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20HELPED%20me%20resolve%20my%20issue%20-%20I%20hope%20it%20helps%20another%20too!%3C%2FLI%3E%3C%2FOL%3E%3C%2FOL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-690602%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20not%20sorting%20numbers%20correctly%20-%20smallest%20to%20largest%20etc.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-690602%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356677%22%20target%3D%22_blank%22%3E%40KerrilynnAZ%3C%2FA%3E%26nbsp%3BThats%20how%20I%20ended%20up%20solving%20the%20problem%20for%20me%20%3A)%20Copying%20data%20indeed%20somehow%20created%20characters%20that%20I%20couldn't%20see%2C%20but%20with%20the%20%3DCLEAN%20function%20and%20the%20steps%20you%20described%20I%20could%20sort%20them%20correctly.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-690642%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20not%20sorting%20numbers%20correctly%20-%20smallest%20to%20largest%20etc.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-690642%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F321331%22%20target%3D%22_blank%22%3E%40DLans%3C%2FA%3E%26nbsp%3BYOU%20just%20made%20my%20day!%26nbsp%3B%20%3B-)%26nbsp%3B%20It%20makes%20it%20worthwhile%20when%20you%20can%20share%20knowledge%20that%20someone%20else%20finds%20of%20benefit%20-%20especially%20in%20these%20scenario's.%26nbsp%3BI%20hate%20wasting%20time%20trying%20to%20figure%20out%20problems%20that%20I%20can't%20find%20quick%26nbsp%3Banswers%20to%20in%20order%20to%20keep%20me%20moving%20forward%2C%20LOL.%26nbsp%3B%20%26nbsp%3BThank%20You!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Jamie Collins
New Contributor

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 , in the 6200 to 6800 range, missing where they should be and then excel places them in order farther down in the 7000 to 8000 range.  I am sure there is an answer and I did find that version 16.9 and above have issues.  Is anyone else having this issue and if so, what can be done?

11 Replies

Nope, in my case it works as expected. Are your numbers are integers in Excel, or that texts? Did you generate them manually (e.g. fill series) or copy/pasted from Web for example?

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.

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.

@Sergei Baklan 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!

@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

@Sergei Baklan, can you explain in detail how to do this, how do I apply this, where do i type it, find it, etc.

 

 

These replies are not helping me troubleshoot my issue - 
I have no hidden columns, no formulas, nothing but raw data in my spreadsheet - yet when I sort from small to large or vice versa with cell formatting in "number" format, it sorts the majority correctly then puts several at the bottom that are out of sequence.  I've copied the cell format of one that sorts correctly and pasted to those that didn't, then resorted to see if it worked but it doesn't.  I don't know what to do to fix this major annoying problem - any suggestions are appreciated.

@KerrilynnAZ , if in any empty cell you try =ISNUMBER() on one the cells which are in the bottom, what will be the result?

@Sergei Baklan  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!

@KerrilynnAZ Thats how I ended up solving the problem for me :) Copying data indeed somehow created characters that I couldn't see, but with the =CLEAN function and the steps you described I could sort them correctly. 

@DLans YOU just made my day!  ;-)  It makes it worthwhile when you can share knowledge that someone else finds of benefit - especially in these scenario's. I hate wasting time trying to figure out problems that I can't find quick answers to in order to keep me moving forward, LOL.   Thank You!