How to sort a list excluding the, an, a

Question

I have a list of books that I would like to alphabetize by title but I want to exclude the first word if it is the, a, or an.

Answer 1

Hi,

In cell B2, enter this formula

=LET(r,A2:A5,SORTBY(r,REGEXREPLACE(r,"^(A|An|The)\s+","",1,1),1))

Hope this helps.

Answer 2

Hello,

I am afraid you would need to have a helper column first.

This is what I use for the result below in the B column. Then you just need to sort the B column.

=LET(t,A2,w,TEXTBEFORE(t&" "," "),IF(ISNUMBER(XMATCH(LOWER(w),{"the","a","an"})),IFERROR(TEXTAFTER(t," "),t),t))

Answer 3

You can do this with a helper column that strips the leading article, then sort by that column instead of the title column directly.

1. In the cell next to your first title (e.g., B2 if titles are in A2), enter this formula:

=TRIM(IF(LEFT(A2,4)="The ",MID(A2,5,255),IF(LEFT(A2,3)="An ",MID(A2,4,255),IF(LEFT(A2,2)="A ",MID(A2,3,255),A2))))

2. Fill it down for all your rows.
3. Select your data, go to Data > Sort, and sort by the helper column (not the title column) A to Z. Your titles will reorder along with it.
4. Hide or delete the helper column once you're done.

One thing to watch for: this formula is case-sensitive and expects a single space after the article ("The ", "A ", "An "). If your titles aren't consistently capitalized, wrap each LEFT(...) comparison in LOWER() so it matches regardless of case, e.g. LOWER(LEFT(A2,4))="the ".