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) ", "")
