Google Sheets – Alphabetical Sorting Ignoring ‘A’, ‘An’, ‘The’

google sheetsgoogle-apps-scriptsorting

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.

Best Answer

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);});
  range.setValues(values);
}

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++) {
    trimmedTitles.push([trimmed(titles[i][0])]);
  }
  var newColumn = sheet.getRange(1, width+1, height, 1);
  newColumn.setValues(trimmedTitles);
  sheet.getRange(1, 1, height, width+1).sort(width+1);   // see comment below
  newColumn.clear();
}

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.

Related Topic