sorting alphanumeric

C

Chronos

hello, I hope this problem I am having can be simply solved....

I need to sort all in one column the following sequence ( I use it for
managing keys that are made. Subsequent keys are enumerated by adding the
next sequential letter)
ex: 101, 102,102a,102b,103
Current any sort I use sorts it this way :101, 102, 103, 102a, 102b

What can I do to sort chronologically with the letters in the same column so
the result looks like this:
101, 102,102a,102b,103

If I add a key to 101, it needs to sort this way:
101,101a,102,102a,102b,103

can any one help, thank you
 
H

Héctor Miguel

hi, Chronos !
... I hope this problem... can be simply solved...
I need to sort all in one column the following sequence... ex: 101, 102,102a,102b,103
Current any sort I use sorts it this way :101, 102, 103, 102a, 102b
... to sort chronologically with the letters in the same column so the result looks like this:
101, 102,102a,102b,103
If I add a key to 101, it needs to sort this way: 101,101a,102,102a,102b,103
Click to expand...

one -possible- way and if it's ok for you to 'have to' use a 'helper' column...
assuming first key in [b2], sort by a column with a formula like the following:
=sumproduct(value(code(mid(b2,choose(1+(len(b2)=4),{1;2;3},{1;2;3;4}),1))&rept("0",choose(1+(len(b2)=4),{7;5;3},{7;5;3;0}))))

if you are planing to generate keys including letters for the first 3 characters...
you might have to change the 'zero' array from: 7;5;3 to: 10;7;4

hth,
hector.
 
Top