+ Reply to Thread
Results 1 to 5 of 5

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

Hybrid View

  1. #1
    Kostis Vezeridis
    Guest

    Re: 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.




  2. #2
    Mr B
    Guest

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

    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.

    >
    >
    >


  3. #3
    Jim Cone
    Guest

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

    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.



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1