How can I sort text (in excel) while ignoring articles (e.g., the

H

happy1

I work in a library and want to sort lists of CD titles. I want to sort the
list alphabetically while ignoring articles (e.g., the, a, an) when they
appear as the first word in the title.
 
N

Nicky

Hi
I don't think you can do this.
what might work is to include the names in an adjacent column an
eliminate the articles, then sort on that.
For example, this formula will eliminate the words "The ", "An " and "
" from the left of the text in cell A1 (note the addition of a spac
after the word in the formula, to prevent all words begining with "A
being cropped):

=IF(LEFT(A1,2)="A ",RIGHT(A1,LEN(A1)-2),IF(LEFT(A1,3)="A
",RIGHT(A1,LEN(A1)-3),IF(LEFT(A1,4)="The ",RIGHT(A1,LEN(A1)-4),A1))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Centered text that does not stay centered 0
Word Template where user inputs <TITLE> and then that can be displayed in footers or headers [MAC] 0
excel 2011 won't sort alphabetically 7
Cannot sort a table containing text 5
Adding a big matrix in Word 2013 5
How to make repeating drop-down lists? 2
Export BPMN Visio Chart to Excel 0
Enhanced paragraph sort - is it possible? 0

Top