Default Sort Order

  • Thread starter Thread starter Ken Wilkes
  • Start date Start date
K

Ken Wilkes

I am trying to find a means to accomplish a true
alpha/numeric sort. The current default sort orders
places numerics before alphas but I need to sort alphas
before numerics. This is being used to sort a parts list
which has mixed characters throughout. Any assistance in
this endeavor would be greatly appreciated.

Ken Wilkes
 
If you are interested only in changing how the first character is interpreted/sorted, you could
use a helper column with a formula like =IF(LEFT(A1,1)>="A",0,1) Then sort with that column as
the 1st key and the part number as the 2nd key.

But if you want to deal with embedded letters, to, i.e. to have 1A123 sort ahead of 11123, the
above will not work. There is no way to change the built-in sort. First, you will have to have
all of the part numbers entered as text, even those that consist only of digits.

The you will probably need to develop a macro. I envision a UDF which creates a new string in
which the numbers are translated to characters which come after "Z". That new string would be
placed in a separate column which would be used as the key for the sort.
 
With your existing partnumbers in Column E - Temporarily create a new
column F
and enter in F2 (say in this example):

=IF(AND(CODE(LEFT(E2,1))>=49,CODE(LEFT(E2,1))<=57),"zz"&E2,E2)

and Copy down. This will Append a "zz" at the beggining of all part
numbers that start with a numeric. Copy and Paste-Special Values of
Completed Column F,

Sort as usual -- All numeric part numbers should be at the bottom preceeded
with "zz".
Now let's fix them back -- in column G (temp column) enter:

=MID(G6,3,LEN(G6)-2) << where G6 is your first ZZ123 - Copy down

On G Column Copy and Paste-Special Values.
Move corrected G Col Numerics over to Col F << Which is your data sorted
as you want it.

Hum,,,,
Is this too much, or not LOL
 
Back
Top