excel data sort question

[shortround]

is it possible when sorting data alphabetically, to tell it to ignore the word 'the'? so for example, if i had a title 'the usual suspects', it would sort that title under 'u' instead of 't'. i would like to do this so i don't have to write 'usual suspects, the', but i can find no options to implement this. thanks.

[calibrax]

Yes you can, but what you have to have is an additional column with a formula which will exclude the article ('the', 'a' or 'an'). So if the first line of your data is in a1, create a formula in b1 as follows:

=IF(LEFT(A1,2)="A ",RIGHT(A1,LEN(A1)-2),IF(LEFT(A1,3)= "An ",RIGHT(A1,LEN(A1)-3),IF(LEFT(A1,4)="The ",RIGHT(A1, LEN(A1)-4),A1)))

Copy the formula down so that all your data is converted, and then you can hide column B... now when you select your data (be sure to select column B as well), just sort in order of column B and not column A.

Hope this helps!