Sorting ignoring alpahnumeric lists ignoring "the" and "an".

[pbrute]

I am trying to sort a large alphanumeric list of titles, but wish to do
so igniring definitive articles such as "The" and "an".

Is there anyone that can tell me how to do this?

 

[pbrute]

Will try your suggestion!!

 

[David McRitchie]

Would modify Tom's suggestion slightly so that you are actually removing words
and not possibly character strings at the end of a word. Also Substitute
is case sensitive so you want to work with either uppercase or lowercase.

You can only nest to a level of 7 so you can add only two more substitutions
to the following: ( I added one additional substitute to Tom's)

=TRIM(substitute(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" " & LOWER(A1) & " "," the ","")," an ","")," and ","")," a ",""))

Somewhat along these lines, is a macro on one of my pages proper.htm
which will allow the first word to be capitalized but the not capitalize
certain words within, which is a lot better than simply using the
PROPER worksheet function..

 

[pbrute]

Thank you for taking the time to respond, but your suggestions are not
working in my case.

I am trying to compile a list of titles that have The, An, A on th the
title, but wish to sort ignoring these indefinitive articles.

Using your formula, the title "A Room with a View" ends looking like
"room withview", and therefore does not sort correctly.

What would work is a formula that would ignore the first three letters
during sorting if they equal "The", "A", or "An".

Thanking you in advance....

 

[Peo Sjoblom]

=TRIM(IF((ISNUMBER(FIND(" ",A1))),IF(OR(LEFT(A1,FIND("
",A1)-1)={"A","An","The"}),SUBSTITUTE(A1,LEFT(A1,FIND("
",A1)-1),"",1),A1),A1))

 

[Peo Sjoblom]

Note that there should be a space between the " " in the find part,
I imagine it is easy to stripa that away if the lines are wrapping

--

Regards,

Peo Sjoblom

=TRIM(IF((ISNUMBER(FIND(" ",A1))),IF(OR(LEFT(A1,FIND("
",A1)-1)={"A","An","The"}),SUBSTITUTE(A1,LEFT(A1,FIND("
",A1)-1),"",1),A1),A1))

 

[pbrute]

Peo,
Thank you so much for taking the time to write up the formula, it now
works perfectly. I would not have been able to figure this out on my
own.

Thank you again!