ASCII Sort

What makes you think Excel will not sort correctly?

Go to Excel help "How do I do an ASCII sort"
one of the options is default sort order. Look at it and compare to an ASCII table. It is not necessarily "wrong", I just need ASCII sort order!! By the way I have verified the Excel "Normal" order numerous times for the last 2 days.
This message was edited by jgg5 on 2002-07-17 13:55

How long are your text strings?

From 1 to 18 characters

I attempted to define a "custom List" with the characters in ASCII order, but appears to only want whole cell entries. One way that can get me "close enough" is to convert key column to all Upper case, dupilcate it, change all underscores to spaces (spaces always sort last in Excel) in the duplicate. Sort on this modified column. Seems like a really poor solution but it should do the minimum of what I need.

What about calculating a checksum for each value and sorting on them? Something like...

{=SUM(CODE(MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1))*ROUND(GROWTH({10,100000}},,LEN(A4)+1-ROW(INDIRECT("1:"&LEN(A4)))),0))}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".


Note: After some evaluation I modified the formula shown above. I'm still not totally satisfied with the results. Perhaps, someone can suggest a modification.
This message was edited by Mark W. on 2002-07-17 16:03

Thanks for the help I'll try the formula. Do you know where I get information on building custom lists? If I can get it right it seems like it maybe a better way to go. The help is almost non-existant.

Jim Wood

Jim, I don't think that a custom list will do the trick for you. There are too many combinations of {"0"-"9","A"-"Z","a"-"z","_"}.

Here's my last attempt (tonight) at refining a checksum formula...

In column B:B I pad a variable length text value with trailing spaces to insure that all values are of equal length (18 characters). You can sort on the value produced in column C:C with the array formula...

{=TEXT(SUM(CODE(MID(B4,ROW($1:$9),1))*ROUND(GROWTH({1,10},,10-ROW($1:$9)),0)),REPT(0,11))&SUM(CODE(MID(B4,ROW($10:$18),1))*ROUND(GROWTH({1,10},,19-ROW($1:$9)),0))}

I'm fairly satisfied with these results, but more testing is warranted.
This message was edited by Mark W. on 2002-07-17 17:58