How to eliminate leading the, a, or an when sorting a column

Question

I want to sort a column of book titles but ignore the, a, or an

Answer 1

Hi,

In cell B2, enter this formula

=LET(rng,A2:A8,SORTBY(rng,TRIM(REGEXREPLACE(rng, "(?i)^\s*(?:a|an|the)\b\s*", "",,1)),1))

Hope this helps.

Answer 2

... ignore the "a" or "an".

For the poster, I personally would "greatly" simplify that suggestion, and just sort...

If I am not mistaken, the t's should be further down on the list

Answer 3

Perhaps not the prettiest solution but you could remove the "a " and "an " first as demonstrated in the picture below, and then you can sort the list.

Answer 4

Hi @ Nancy Read,

Try below formula:

=BYROW(IFNA(SORT(TEXTSPLIT(TEXTJOIN(";",, A2:A19), " ", ";"), 2), ""), LAMBDA(a, TEXTJOIN(" ",, a)))

Hope this helps.

IlirU

Answer 5

To eliminate leading articles such as "the," "a," or "an" when sorting a column of book titles, you can use a derived column in your SQL query that removes these articles before sorting. For example, you can use the LTRIM and RTRIM functions along with REPLACE to clean up the titles. Here’s an example SQL query that demonstrates this:

SELECT title
FROM titles
ORDER BY LTRIM(REPLACE(REPLACE(REPLACE(title, 'the ', ''), 'a ', ''), 'an ', ''));

This query replaces the leading articles with an empty string before sorting the titles. You can adjust the query according to your specific database and requirements.

---

References:

• Sort rows (Visual Database Tools)
• Sort columns
• Column.Sort Method
• Filter by values in a column