How to modify the 'sort order' setting in Excel?

[Gren957]

I archive all my DVDs.

Excel sorts data by using the following ascending sort order:
0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > a b c d e f g h i j k l m n o p q r s t u v w x y z A B C D E F G H I J K L M N O P Q R S T U V W X Y Z.

1.) I prefer to skip the articles “A”, “An”, “The.”

2.) Numerically, how do I increase the ascending sort order? For example:

CURRENTLY:
Title Grade Genre Year (etc.)
1408
2012
2 Fast 2 Furious
28 Days Later
3:10 to Yuma

IDEAL:
Title Grade Genre Year (etc.)
2 Fast 2 Furious
3:10 to Yuma
28 Days Later
1408
2012

How do I modify these specific settings in Excel?

-Thank you very much

[mrice]

A good approach would be to create a separate 'helper' column which has those parts of the title that you want Excel to sort and then use the existing sort functionality on that column.

[martindwilson]

i dont see how you can
1408
2012
2 Fast 2 Furious
28 Days Later
3:10 to Yuma
first you'd have to separate numbers from text the decide how 3:10 should be handled
ok this will do the second part under ideal
but it doesn't ignore as you asked a . an. the
=IF(ISNUMBER(LEFT(A1)+0),TEXT(LOOKUP(9.99E+307,--MID(SUBSTITUTE(A1,":","^"),MIN(FIND({1,2,3,4,5,6,7,8,9,0},SUBSTITUTE(A1,":","^")&1234567890)),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,":","^")))))),"0000"),A1)