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

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

I was going to post a similar solution, until I realized that it could have
disastrous results.
There's Something about Mary would become: re's Something about Mary, for
example.

Still working on a solution at the moment.
--
John C


"Lars-Ã…ke Aspelin" wrote:

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

"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

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

you know, a simpler formula for the helper column.
B2: =IF(LEFT(LOWER(A2),4)="the ",RIGHT(A2,LEN(A2)-4),A2)

Still minimize the width of col B, and white font format col B, but sort by
col B.
--
John C


"John C" wrote:

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

Wow thanks for the help your 99% the way there, they do sort and "The" is
still there but now the titles with "The" in them are now sorted but the are
still sorted in the list starting at the T's in the list instead of using
the fourth letter to sort. Maybe I can put the word the in another column
and right justify?

Thanks for the help that as impressive!

Rich


"John C" <johnc@stateofdenial wrote in message
...
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

Use my second suggestion, it's easier and more compact. The problem with my
first suggestion is it gets rid of ALL occurrances of the word the, not just
the one at the beginning.
--
John C


"Rich" wrote:

Wow thanks for the help your 99% the way there, they do sort and "The" is
still there but now the titles with "The" in them are now sorted but the are
still sorted in the list starting at the T's in the list instead of using
the fourth letter to sort. Maybe I can put the word the in another column
and right justify?

Thanks for the help that as impressive!

Rich


"John C" <johnc@stateofdenial wrote in message
...
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

Your second suggestion didn't do anything except put the formula in the
cell.

Rich


"John C" <johnc@stateofdenial wrote in message
...
Use my second suggestion, it's easier and more compact. The problem with
my
first suggestion is it gets rid of ALL occurrances of the word the, not
just
the one at the beginning.
--
John C


"Rich" wrote:

Wow thanks for the help your 99% the way there, they do sort and "The" is
still there but now the titles with "The" in them are now sorted but the
are
still sorted in the list starting at the T's in the list instead of using
the fourth letter to sort. Maybe I can put the word the in another column
and right justify?

Thanks for the help that as impressive!

Rich


"John C" <johnc@stateofdenial wrote in message
...
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

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

It sounds as if the column with the formula is formatted as TEXT. Clear the
contents of the helper column, highlight column B, type the formula in B2
again, and then edit--fill--down, and sort by column B.

--
John C


"Rich" wrote:

Your second suggestion didn't do anything except put the formula in the
cell.

Rich


"John C" <johnc@stateofdenial wrote in message
...
Use my second suggestion, it's easier and more compact. The problem with
my
first suggestion is it gets rid of ALL occurrances of the word the, not
just
the one at the beginning.
--
John C


"Rich" wrote:

Wow thanks for the help your 99% the way there, they do sort and "The" is
still there but now the titles with "The" in them are now sorted but the
are
still sorted in the list starting at the T's in the list instead of using
the fourth letter to sort. Maybe I can put the word the in another column
and right justify?

Thanks for the help that as impressive!

Rich


"John C" <johnc@stateofdenial wrote in message
...
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