SOLVED

order a list of numbers

%3CLINGO-SUB%20id%3D%22lingo-sub-2577615%22%20slang%3D%22en-US%22%3Eorder%20a%20list%20of%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2577615%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20order%20a%20list%20of%20numbers%2C%20I%20used%20order%20from%20Z%20to%20A%2C%20but%20it%20put%209%20before%2011%20because%209%26gt%3B1%20(it%20takes%20just%20the%20first%20number)%20how%20can%20I%20fix%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2577615%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2578345%22%20slang%3D%22en-US%22%3ERe%3A%20order%20a%20list%20of%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2578345%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1110546%22%20target%3D%22_blank%22%3E%40IsiI95%3C%2FA%3E%26nbsp%3BYour%20list%20contains%20texts%20that%20look%20like%20numbers.%20When%20you%20sort%20such%20a%20list%2C%20it%20is%20done%20in%20alphabetic%20order.%20And%20then%2C%20a%20text%20like%20%2211%22%20sorts%20before%20a%20text%20like%20%229%22%20when%20you%20sort%20in%20ascending%20order.%20The%20opposite%20is%20true%20when%20you%20sort%20in%20descending%20order.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERather%20than%20just%20pushing%20the%20Sort%20A%20to%20Z%20or%20Sort%20Z%20to%20A%20button%2C%20select%20%22Custom%20Sort...%22%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-07-23%20at%2008.15.25.png%22%20style%3D%22width%3A%20119px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F297913i44DF206883A69F7E%2Fimage-dimensions%2F119x184%3Fv%3Dv2%22%20width%3D%22119%22%20height%3D%22184%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-07-23%20at%2008.15.25.png%22%20alt%3D%22Screenshot%202021-07-23%20at%2008.15.25.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThen%20choose%20your%20desired%20sorting%20options%20and%20press%20OK.%3C%2FP%3E%3CP%3ENow%2C%20Excel%20should%20warn%20you%20for%20the%20fact%20that%20you%20have%20texts%20in%20the%20list%20that%20look%20like%20numbers%20and%20give%20you%20two%20options.%20The%20following%20screen%20should%20pop-up.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-07-23%20at%2008.08.46.png%22%20style%3D%22width%3A%20355px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F297914i537E199F1DE54031%2Fimage-dimensions%2F355x170%3Fv%3Dv2%22%20width%3D%22355%22%20height%3D%22170%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-07-23%20at%2008.08.46.png%22%20alt%3D%22Screenshot%202021-07-23%20at%2008.08.46.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ENow%20you%20can%20fix%20your%20problem%2C%20but%20perhaps%20better%20to%20pay%20attention%20to%20making%20sure%20that%20your%20data%20is%20clean%20before%20you%20start%20manipulating%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I want to order a list of numbers, I used order from Z to A, but it put 9 before 11 because 9>1 (it takes just the first number) how can I fix this?

2 Replies
best response confirmed by IsiI95 (New Contributor)
Solution

@IsiI95 Your list contains texts that look like numbers. When you sort such a list, it is done in alphabetic order. And then, a text like "11" sorts before a text like "9" when you sort in ascending order. The opposite is true when you sort in descending order. 

 

Rather than just pushing the Sort A to Z or Sort Z to A button, select "Custom Sort..."

Screenshot 2021-07-23 at 08.15.25.png

Then choose your desired sorting options and press OK.

Now, Excel should warn you for the fact that you have texts in the list that look like numbers and give you two options. The following screen should pop-up.

Screenshot 2021-07-23 at 08.08.46.png

Now you can fix your problem, but perhaps better to pay attention to making sure that your data is clean before you start manipulating it.

@Riny_van_Eekelen hi!  I couldn't find that option, but I solve it changing dots by comas.