Improper sorting of text using custom sort list

%3CLINGO-SUB%20id%3D%22lingo-sub-1956762%22%20slang%3D%22en-US%22%3EImproper%20sorting%20of%20text%20using%20custom%20sort%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1956762%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20columns%3B%20column%20A%20is%20formatted%20as%20text%20and%20consists%20of%20a%20word%20followed%20by%20a%20space%2C%20then%20numbers%20(1-3%20digits)%2C%20column%20B%20is%20numeric.%20I%20want%20to%20sort%20by%20column%20A%20using%20a%20custom%20list%20rather%20than%20alphabetic%20because%20of%20the%20way%20Excel%20handles%20numbers%20when%20they%20are%20text%20and%20I%20want%20things%20grouped%20in%20a%20certain%20way.%20I%20set%20up%20the%20custom%20list%20and%20this%20looks%20fine.%20For%20some%20reason%2C%20two%20of%20the%20rows%20(colon%203%20and%20TI%20443)%20will%20not%20fall%20into%20the%20correct%20custom%20order.%20They%20will%20sort%20fine%20A%20to%20Z%20but%20it's%20as%20if%20excel%20is%20sorting%20those%20two%20alphabetically%20rather%20than%20by%20custom%20list.%20So%20colon%203%20gets%20placed%20after%20colon%20264%20instead%20of%20after%20colon%202%20like%20I%20want%20it.%3C%2FP%3E%3CP%3EI%20have%20made%20sure%20there%20are%20no%20hidden%20rows%2Fcolumns%2C%20no%20leading%20or%20hidden%20digits%20in%20the%20cells%2C%20formatted%20as%20text%2C%20etc.%20There%20are%20no%20filters%20in%20place%20to%20clear.%20Have%20copied%20and%20pasted%20into%20new%20workbooks%2C%20into%20and%20out%20of%20notepad%2C%20etc.%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20driving%20me%20nuts%20and%20I%20need%20to%20use%20this%20sort%20feature%20on%20multiple%20sheets.%20Please%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1956762%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1956900%22%20slang%3D%22en-US%22%3ERe%3A%20Improper%20sorting%20of%20text%20using%20custom%20sort%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1956900%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BI'm%20not%20sure%20what%20is%20wrong%20with%20the%20file%3F%20Opens%20fine%20for%20me.%20I%20attached%20it%20again%20and%20also%20as%20.xls%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1956857%22%20slang%3D%22en-US%22%3ERe%3A%20Improper%20sorting%20of%20text%20using%20custom%20sort%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1956857%22%20slang%3D%22en-US%22%3Ethere%20appears%20to%20be%20something%20wrong%20with%20your%20attached%20file.%20Could%20you%20try%20it%20again%20please%3C%2FLINGO-BODY%3E
Occasional Contributor

I have two columns; column A is formatted as text and consists of a word followed by a space, then numbers (1-3 digits), column B is numeric. I want to sort by column A using a custom list rather than alphabetic because of the way Excel handles numbers when they are text and I want things grouped in a certain way. I set up the custom list and this looks fine. For some reason, two of the rows (colon 3 and TI 443) will not fall into the correct custom order. They will sort fine A to Z but it's as if excel is sorting those two alphabetically rather than by custom list. So colon 3 gets placed after colon 264 instead of after colon 2 like I want it.

I have made sure there are no hidden rows/columns, no leading or hidden digits in the cells, formatted as text, etc. There are no filters in place to clear. Have copied and pasted into new workbooks, into and out of notepad, etc. 

This is driving me nuts and I need to use this sort feature on multiple sheets. Please help!

10 Replies
there appears to be something wrong with your attached file. Could you try it again please

@mathetes I'm not sure what is wrong with the file? Opens fine for me. I attached it again and also as .xls file.

@poholekc 

 

I'm not sure either. If you go back and look at the original, you'll see that its size is given as 1KB, a fraction of these most recent.

 

HOWEVER, even your most recent generate this error message when I try to download them.

mathetes_0-1607103776197.png

 

@mathetes Trying again...

@poholekc 

 

Whew... finally!

 

Is this in the order you wanted? I created two helper columns, taking apart the text at the space, creating a column on text only and a column of the numbers, sorting the latter as numbers, not as text.

 

@poholekc 

Total length on the list as SUM(LEN(range)) is about 2000 characters, includes invisible characters between items. Last items in your range are not in the list, you may check in dialogue box. Thus they are sorted wrongly. The workaround is to create helper column to the right of your list like 1,2,3,4,... and sort on it.

 

All details are here Custom List Limits - Excel Tips - MrExcel Publishing

@mathetes Thanks for the suggestion. Now I can't open your file, but that's ok. I understand your approach. It bugs me that this needs a workaround like you suggested though when it should work with the original formatting.

@poholekcThat original file somehow got corrupted , was only 1KB in size. Here's what should have been attached.

@Sergei Baklan This is genius. Thank you! Will choose shorter labels in the future to avoid the extra characters.

@poholekc 

Excel initially is more numbers calculator, working with texts we always shall take size limits into account.