Ask Your Question

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

asked 2013-09-15 16:43:21 +0100

titan21 gravatar image

updated 2013-09-15 16:52:18 +0100

oweng gravatar image


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!

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-08 08:30:28.465472

2 Answers

Sort by » oldest newest most voted

answered 2013-09-15 17:06:37 +0100

David gravatar image

updated 2013-09-15 17:09:03 +0100

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?

edit flag offensive delete link more


I'll mark this up for effort :-) 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 gravatar imageoweng ( 2013-09-15 17:27:52 +0100 )edit

@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! :) I guess the formula bascially automates your final option ("Yet another is to have ... a separate column for a sorting key").

David gravatar imageDavid ( 2013-09-15 17:31:08 +0100 )edit

answered 2013-09-15 17:00:24 +0100

oweng gravatar image

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.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2013-09-15 16:43:21 +0100

Seen: 8,395 times

Last updated: Sep 15 '13