12 June 2016

Formulas for Library Catalogue Sort Order

If you use library catalogues, you may notice that book or film titles in English are sorted without the leading article, "A", "An" and "The". For example, this list of titles ...

A Kind of Intimacy
An Awfully Big Adventure
The Girl In The Polka-Dot Dress

... is sorted like this in a library catalogue ...

An Awfully Big Adventure
The Girl In The Polka-Dot Dress
A Kind of Intimacy

To reproduce this sort order in a spreadsheet, create an additional column containing titles without the leading article then sort this column.

Kind of Intimacy
Awfully Big Adventure
Girl In The Polka-Dot Dress

If the title is in cell A1, the formula to transform a title in Excel is this nested IF() formula below, which tests for each possible article at the start of a string and returns the title without the article:

=if(left(A1,2)="A ",mid(A1,3,100),if(left(A1,3)="An ",mid(A1,4,100),if(left(A1,4)="The ",mid(A1,5,100),$B33)))

The equivalent formula is much shorter in Google Sheets because it has regular expressions formulas. The one below simply replaces the leading article in a title with an empty string:

=REGEXREPLACE(A1, "^(A|An|The) ", "")