How do I sort without articles such as "The" and "A"?


Just wondered if there is an easy way to sort a list of films for example so that The Machinist is sorted along with the "M"s rather than the "T"s?

For another example, at the moment, when I sort by alphabetical order, the following occurs:

Texas Chainsaw Massacre
The Abyss

When it should be:

The Abyss
Texas Chainsaw Massacre

Any ideas?

Thanks in advance!

For your first question (getting rid of “The” in your sort order), as it happens, these instructions for ignoring articles while sorting titles in Excel work nicely in Calc, too. Basically, you set up an article-stripping formula in the adjacent column, and the sort on that. Works well, and not too tricky.

As for your second question (about “Texas Chainsaw Massacre The Abyss”), that’s a matter of correct data entry, isn’t it?

I’ll mark this up for effort :slight_smile: That formula =IF(LEFT(A2,2)="A ",RIGHT(A2,LEN(A2)-2),IF(LEFT(A2,3)= "An ",RIGHT(A2,LEN(A2)-3),IF(LEFT(A2,4)="The ",RIGHT(A2, LEN(A2)-4),A2))) is not the simplest or prettiest solution. I would change the data myself, but good find.

@oweng - like you, my first thought was simply to split the data, have an “article” column, and display the full name with an expression. On reflection, though, that potentially makes data entry slower, and you couldn’t (e.g.) copy/paste in one step. This would the advantage of the article-stripping formula, I think. Still, more than one way to skin a cat! :slight_smile: I guess the formula bascially automates your final option (“Yet another is to have … a separate column for a sorting key”).

The problem is data-related more than sort-related. Ever wonder why, when you go to the video store they have entries on their system like “Abyss, The”? This is why. There are a few different approaches, and the one I have indicated is possibly the simplest. Another is to use a separate column for pronoun prefixes like “The” and “A”. Yet another is to have a display column with the full title and a separate column for a sorting key, which could have the shorter title.