I have a Movie list using Google Sheets that I would like do some sorting with using a script rather than a formula.

Column A is the movie Title. I would like to be able to sort the movie titles alphabetically but ignore when a movie title BEGINS with "A", "An" and "The". For example: "The Abyss" would be filed under 'A' for "Abyss" but would still display as "The Abyss".

I would also like it to ignore a few other words that I can continue to enter as needed. For example: "Marvel's"... So that "Marvel's Iron Man" would be filed under 'I' for "Iron Man" but would still display as "Marvel's Iron Man".

I know how to do it with this formula in a new column:

=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)

But I would rather do it through a script so that I don't need to use another column for this. When someone clicks to sort movie titles A-Z, it would be nice if it ignored "A", "An", and "The" without having to use an additional column of titles. It's less messy and takes up less space.

Also, if there is a way through the script for it to first sort by Movie Title and then by release Date by default, that would be great. But of course, it would still be nice not if it weren't locked into that sorting order so that friends and family can sort as they like (by year, etc.) when browsing our catalog. This is not as important of a feature, though.

  • Did you already tried to write a script? Sep 8, 2015 at 21:03
  • I tried messing around with a few that I found online but I've had no luck so far. I'm not great with scripts unfortunately. I do ok with some tweaking of existing scripts sometimes but I'm not very good at writing them from scratch. Sep 8, 2015 at 21:39
  • Add those details to your question including if those scripts worked for you before you tweak them to fit your needs. Sep 8, 2015 at 21:43
  • Sorry, I should have been more clear. I didn't really find any for this purpose. The ones that I found really were not for this purpose which is why I didn't have any luck with them. I more just meant that in general I do better with a little tweaking as opposed to writing scripts from scratch. Sep 9, 2015 at 1:36

1 Answer 1


Managing expectations

Scripts cannot intercept a user's click on column name. This action will always invoke built-in search. So, if you want users to sort by clicking a column, you need a separate column for sorting.

With a separate column

You don't need a complex formula with IFs to remove articles or other words: a regular expression can be used to handle all variants at once:

= regexreplace(A1, "(?i)^(a|an|the) ", "") 

(case-insensitive replacement), or

= regexreplace(lower(A1), "^(a|an|the) ", "") 

(also converting to lowercase, so that the sorting is case-insensitive).

This is easily extended to other words: (a|an|the|Marvel's) and so on.

With a script, for formula-less sheets

But if you really don't want another column, here's a script. What it does:

  • Adds a "Sort > by Movie Title" command to the menu (when the spreadsheet is opened).
  • When invoked with this command, sorts entire sheet A>Z by the first column, ignoring beginning a, an, the and also ignoring case.
  • Does not sort by any other column (adding year, etc as a secondary criterion is up to you).

As a side effect, this script will remove any formulas from the sheet, replacing them with the current values in those cells. Which is unacceptable, unless you don't use formulas.

function sortByTitle() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange(); 
  var values = range.getValues();
  values.sort(function(a,b) {return (trimmed(a[0]) < trimmed(b[0]) ? -1 : 1);});

function trimmed(str) {
  return str.toLowerCase().replace(/^(a|an|the) /, ''); 

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Sort", [{name: "by Movie Title",  functionName: "sortByTitle"}]);

With a script, preserving formulas

This is a hybrid approach: it adds a new column filled with trimmed titles, invokes the method sort of the Range class, then clears the column. As a result, formulas are preserved (if your formulas make relative references to cells in other rows, sorting will still mess things up, but this is to be expected).

Here is the new sortByTitle function; the functions trimmed and onOpen remain as above.

function sortByTitle() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var height = sheet.getDataRange().getHeight();
  var width = sheet.getDataRange().getWidth();
  var titles = sheet.getRange(1, 1, height, 1).getValues();
  trimmedTitles = [];
  for (var i=0; i<titles.length; i++) {
  var newColumn = sheet.getRange(1, width+1, height, 1);
  sheet.getRange(1, 1, height, width+1).sort(width+1);   // see comment below

If your spreadsheet contains a header row (which is likely), replace the line with the comment with

  sheet.getRange(2, 1, height-1, width+1).sort(width+1);

so that the top row is excluded from sorting.

  • Thank you. I wound up using the regexreplace formula in place of the IF formula I had been using. It's much more simple. Unfortunately, I have lots of other formulas in the sheet so the script is not an option if it will strip other formulas from the sheet. I'm still hoping for a script option but if this is the best I can get in the next few days, I'll mark it as the best answer. Thank you. Sep 9, 2015 at 6:46
  • 1
    I added a solution for preserving formulas.
    – user79865
    Sep 9, 2015 at 22:36
  • 1
    Using the 3rd method, it's easy to add sorting by a secondary column: see documentation of range.sort()
    – user79865
    Sep 9, 2015 at 23:11
  • Sorry for the super late reply. I had lost track of this after I wound up using an array formula so I only needed one formula in a second header row instead of in every row. But I love the idea of using your first solution as an array since it's much more simple than my formula. Can it be easily edited as an array? And I love the idea of using a script instead to eliminate the sorting column all together. Using the addition you gave for headers, which part do I need to edit to allow for a second or third header row? Oct 27, 2016 at 4:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.