On Tue, 22 Jul 2008 10:09:57 -0500, "Rich" <dont@localhost wrote:


"Lars-Åke Aspelin" wrote in message
.. .
On Tue, 22 Jul 2008 09:49:42 -0500, "Rich" <dont@localhost wrote:

I have a list of movies and I want to sort them by name then what stack
there in.
a b
Gone with the wind | stack 7
The god's must be crazy | stack 6

Except I want to ignore the word "the" in the title but still have it in
the
title after the list is sorted alphabetically.

Easy to do? Is there a way to hide the word and it will sort OK ?

Thanks for any help, Rich


Try this formula in cell C2:

=TRIM(SUBSTITUTE(LOWER(A2),"the",""))

Copy down as many rows as needed.
Then you sort by column C.
Hide or remove column C if you want.

Hope this helps / Lars-Åke


It does sort correctly but it removes the word The so then the title doesn't
read correctly.

I want the word the there I just want the list sorted correctly but titles
with "the" are all grouped together? So I need something that sees the word
"The" sorts by the fourth letter instead of the first when this occurs.

Thanks for the help, Rich



What do you mean? Nothing is removed from column A. It still contains
the original titles.
And the helper column, column C ,is not for reading. It is only to be
used for the sorting and can be hidden or even removed after the
sorting is done.

Lars-Åke