Sorting movie titles? skipping The, A, An etc?

Howdy,

I have a spreadsheet with all the movies I own. I want to sort it out by
title but I don't want it to sort based on the word The, A, An, etc. Is
there any way to do this in Excel without typing the name with the word at
the end?

I don't want to type the names like:
Mummy, The
But I want it to sort based on the M.

Thanks.

Copy all of the titles to a helper column
Sort by the helper column
copy everything to a second helper column
for all of the Titles starting with "A ..."
in the second helper column
=right(H1,len(h1)-2)
to get rid of the A space
for all of the "An ..."
=right(H1,len(H1)-3)
for all of the "The ...
=right(H1,len(H1)-4)
copy the second Helper column and paste special values over the first helper
column and sort by this column
Whenever you add a new movie add the full title to the main list and an
appropriately modified title to the second list
and sort by the second list


"Mr B" wrote:

> Howdy,
>
> I have a spreadsheet with all the movies I own. I want to sort it out by
> title but I don't want it to sort based on the word The, A, An, etc. Is
> there any way to do this in Excel without typing the name with the word at
> the end?
>
> I don't want to type the names like:
> Mummy, The
> But I want it to sort based on the M.
>
> Thanks.

Let us say you have the titles in column A:A
In an auxiliary column put the articles you want to ignore. Let us say in
K1:K3 you have "The", "A", "An".
Now, in B2 you use the following formula:
=IF(ISNA(VLOOKUP(LEFT(A2,FIND("
",A2)-1),$K$1:$K$3,1,0)),A2,IF(ISERROR(FIND(" ",A2)), A2, MID(A2,FIND("
",A2)+1,LEN(A2))))

This should strip your title name of the article, if it starts with one.
Expand the list of articles as necessary, changing the $K$1:$K$3
accordingly. Sort by column B:B.

HTH
Kostis Vezerides

"Mr B" <MrB@discussions.microsoft.com> wrote in message
news:F2947FA0-B5EA-4B95-8AD6-5D66C1870A3F@microsoft.com...
> Howdy,
>
> I have a spreadsheet with all the movies I own. I want to sort it out by
> title but I don't want it to sort based on the word The, A, An, etc. Is
> there any way to do this in Excel without typing the name with the word at
> the end?
>
> I don't want to type the names like:
> Mummy, The
> But I want it to sort based on the M.
>
> Thanks.

I was hoping someone might have made an add-in that would be specifically for
sorting things when it's a book/movie title... heh.

Guess not though...


"Kostis Vezeridis" wrote:

> Let us say you have the titles in column A:A
> In an auxiliary column put the articles you want to ignore. Let us say in
> K1:K3 you have "The", "A", "An".
> Now, in B2 you use the following formula:
> =IF(ISNA(VLOOKUP(LEFT(A2,FIND("
> ",A2)-1),$K$1:$K$3,1,0)),A2,IF(ISERROR(FIND(" ",A2)), A2, MID(A2,FIND("
> ",A2)+1,LEN(A2))))
>
> This should strip your title name of the article, if it starts with one.
> Expand the list of articles as necessary, changing the $K$1:$K$3
> accordingly. Sort by column B:B.
>
> HTH
> Kostis Vezerides
>
> "Mr B" <MrB@discussions.microsoft.com> wrote in message
> news:F2947FA0-B5EA-4B95-8AD6-5D66C1870A3F@microsoft.com...
> > Howdy,
> >
> > I have a spreadsheet with all the movies I own. I want to sort it out by
> > title but I don't want it to sort based on the word The, A, An, etc. Is
> > there any way to do this in Excel without typing the name with the word at
> > the end?
> >
> > I don't want to type the names like:
> > Mummy, The
> > But I want it to sort based on the M.
> >
> > Thanks.
>
>
>

Mr B,

After reading your post, I have started work on an Excel add-in
that will do seven different special sorts, including one that omits articles.
It could be done in a few more days...
If you would like to give it a try, then email me and I will forward
it upon completion. - remove XXX from my email address.
(will send it to all that make a request)

Jim Cone
San Francisco, USA
jim.coneXXX@rcn.comXXX



"Mr B" <MrB@discussions.microsoft.com> wrote in message
news:2A74C34E-B76D-4D53-A463-5A3B8D91D293@microsoft.com...
I was hoping someone might have made an add-in that would be specifically for
sorting things when it's a book/movie title... heh.

Guess not though...


"Kostis Vezeridis" wrote:
> Let us say you have the titles in column A:A
> In an auxiliary column put the articles you want to ignore. Let us say in
> K1:K3 you have "The", "A", "An".
> Now, in B2 you use the following formula:
> =IF(ISNA(VLOOKUP(LEFT(A2,FIND("
> ",A2)-1),$K$1:$K$3,1,0)),A2,IF(ISERROR(FIND(" ",A2)), A2, MID(A2,FIND("
> ",A2)+1,LEN(A2))))
> This should strip your title name of the article, if it starts with one.
> Expand the list of articles as necessary, changing the $K$1:$K$3
> accordingly. Sort by column B:B.
> HTH
> Kostis Vezerides

>
> "Mr B" <MrB@discussions.microsoft.com> wrote in message
> news:F2947FA0-B5EA-4B95-8AD6-5D66C1870A3F@microsoft.com...
> > Howdy,
> > I have a spreadsheet with all the movies I own. I want to sort it out by
> > title but I don't want it to sort based on the word The, A, An, etc. Is
> > there any way to do this in Excel without typing the name with the word at
> > the end?
> > I don't want to type the names like:
> > Mummy, The
> > But I want it to sort based on the M.
> > Thanks.