Custom sort order

Posted by John H on February 13, 2002 5:43 AM

I have a list of items, some prefixed by numbers, which I'd like to sort alphabetically, rather than alphanumerically. Is this possible without resorting to VB?

Thanks for any help in advance.

Posted by Barrie Davidson on February 13, 2002 6:21 AM

John, is the prefix constant (i.e., it is always 3 numbers)?

BarrieBarrie Davidson

Posted by John H on February 13, 2002 6:33 AM

No, although if a number is used as a prefix, there will always be a hyphen before the first letter of the word.

Posted by Barrie Davidson on February 13, 2002 7:36 AM

Okay, that makes it easy. In adjacent column put this formula (assumes the data is in column A):

=IF(ISERROR(FIND("-",A1)),A1,MID(A1,FIND("-",A1)+1,LEN(A1)))

Copy this formula down and then sort on that column.

Regards,
BarrieBarrie Davidson

Posted by Mudface on February 13, 2002 7:40 AM

Great, thanks very much Barrie!