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

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.