Okay, create a 'helper' column, I inserted a column before Column B (which
has the Stack #). And in B2, and copied down, I used the following formula:

=SUBSTITUTE(LOWER(IF(AND(LEFT(LOWER(A1),4)="the ",RIGHT(LOWER(A1),4)="
the"),MID(A1,5,LEN(A1)-8),IF(LEFT(LOWER(A1),4)="the
",RIGHT(A1,LEN(A1)-4),IF(RIGHT(LOWER(A1),4)="
the",LEFT(A1,LEN(A1)-4),A1))))," the "," ")

Then you can change column B to very narrow(I usually use a column width of
0.5), and can change the cells to have 'white fonts' so that nothing is
really visible. Then when you sort your selection, cell A2 through cell C1000
(or whatever is the end of your data), your sort criteria will be ascending
for column B, but column A will still be what you see.

--
John C


"Rich" 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