Creative Sorting with Letters and Numbers

[Solon]

Greetings,

We use Excel 2003 and sometimes have a need to sort data that includes
figure references with the section and figure indicators in one column
(Section B, Figure 1 would be "B-1" in that cell).

When we sort, however, we find that figure B-10 will come before B-2. We
understand that Excel is sorting in such a way that it's not 'ten', it's
'one-zero' which comes before two.

We've tried using different cell formatting (General, Number, even Custom)
to no avail, though some of the formats in "Custom" appeared intriguing.

Is there a way to format the cells and/or change sort properties so that a
"B-10" is seen as 'more' than "B-2" so that "B-2" shows up before "B-10"??

Thanks,

Solon

 

[Dave Peterson]

I'd use B-02 instead of B-2.

Or split the data into two helper columns--one for the letter and one for the
number and use those helper columns to sort the data.

 

[Solon]

Dave,
Thanks, actually it's head-slap time for me.

We take two columns Section and Figure, combine them with a handy little
macro, then sort. So I looked at your suggestion, slapped my head, and
re-wrote the macro to do a quick sort THEN combine the columns.

Sometimes the most obvious solution is also the one you just can't see!


Thanks,

Solon

 

[Dave Peterson]

I hate when that happens! <vbg>

Dave,
Thanks, actually it's head-slap time for me.

We take two columns Section and Figure, combine them with a handy little
macro, then sort. So I looked at your suggestion, slapped my head, and
re-wrote the macro to do a quick sort THEN combine the columns.

Sometimes the most obvious solution is also the one you just can't see!

Thanks,

Solon