Sorting by more than one value

[jeff lane]

I want to sort 18000 records alphanumerically by more than one value as
below

t123abc m123abc
mx54bcd m123cde
m123abc t123abc
t123bcd t123bcd
mx04bcd fx04abc
mx04abc mx04abc
fx04abc mx04bcd
mx54abc fx54abc
fx54abc mx54bcd
m123cde mx54abc


The row on the left is unsorted.
The row on the right is sorted alphabetically by the first letter then by
the numbers then alphabetically. However, if there is a second letter it is
sorted alphabetically by the first and second letter and then by the next
two numbers then alphabetically.
The first number is always 0 or 5, the second number changes according to
the year.
Any help gretefully received.

jeff

 

[Bob Phillips]

You need helper columns, extracting the data out by formulae, and then sort
by the helper columns

 

[Myrna Larson]

Your description doesn't match your example. You say if there are two letters,
you sort by the 2 letters, then by the 3 numbers, then by the last 3 letters.

If your example is correct, when there are 2 letters followed by 2 numbers,
the *numbers* are the 1st key, and the leading letters the 2nd key (you have
mx04 before fx54 -- f comes before m, no?). But there's still a problem: the
last 2 entries are out of order.

OTOH, if your *verbal* description is correct, the order should be

m123abc
m123cde
t123abc
t123bcd
fx04abc
fx54abc
mx04abc
mx04bcd
mx54abc
mx54bcd

If the above sorted list is correct, you can achieve that with one helper
column which creates an 8 character code consisting of 2 letters, 3 digits,
and 3 letters. The formula below adds a leading space if there's only 1
initial letter; it inserts a 0 at position 3 if there are 2 initial letters.

=IF(ISNUMBER(--MID(A1,2,1))," "&A1,LEFT(A1,2)&"0"&MID(A1,3,255))

Then sort on that helper column.