+ Reply to Thread
Results 1 to 3 of 3

Sorting text that begins with "the" or "a"

  1. #1
    kcg727
    Guest

    Sorting text that begins with "the" or "a"

    I'm trying to sort a list in Excel and one of the columns is a list of
    titles. When I sort I want it to ignore the words "The" and "A" at the
    beginning. (for example, I want "The Piano" to be with the Ps not the Ts).

    I'm about to throw my computer across the room because I can't figure it out
    and can't find anything in the Help sections that gives me the right answer.
    I'm not the most savvy with Excel, but this really shouldn't be that hard.
    Help me.

  2. #2
    Niek Otten
    Guest

    Re: Sorting text that begins with "the" or "a"

    <I'm not the most savvy with Excel, but this really shouldn't be that hard.>

    Well, Excel wasn't exactly made to do this kind of job, but you are right;
    it can be done.
    Are you sure there will not be instances where two words should be skipped?
    Or "The" not skipped if the 3rd word is as special one?

    Anyway, make a list of all your skip words in a column.
    I had mine in H1:H3 (The, A and One) and the titles in A1:A7
    In B1, I put this formula:

    =IF(ISERROR(FIND(" ",A1)),A1,IF(ISNA(VLOOKUP(LEFT(A1,FIND("
    ",A1)-1),$H$1:$H$3,1,FALSE)),A1,RIGHT(A1,LEN(A1)-FIND(" ",A1))))

    And copied it down

    It skipped The, A and One, not other first words like All

    Now you can sort on column B

    --
    Kind regards,

    Niek Otten


    "kcg727" <[email protected]> wrote in message
    news:[email protected]...
    > I'm trying to sort a list in Excel and one of the columns is a list of
    > titles. When I sort I want it to ignore the words "The" and "A" at the
    > beginning. (for example, I want "The Piano" to be with the Ps not the
    > Ts).
    >
    > I'm about to throw my computer across the room because I can't figure it
    > out
    > and can't find anything in the Help sections that gives me the right
    > answer.
    > I'm not the most savvy with Excel, but this really shouldn't be that hard.
    > Help me.




  3. #3
    Steve Yandl
    Guest

    Re: Sorting text that begins with "the" or "a"

    Here is one option. Assuming the titles are in column A, create a helper
    column that you will sort by and drag down the formula

    =IF(LEFT(A1,2)="A ",RIGHT(A1,LEN(A1)-2),IF(LEFT(A1,4)="The
    ",RIGHT(A1,LEN(A1)-4),A1))

    Remaining in the helper column will be the titles stripped of The{plus
    space} or A{plus space} wherever they appear at the start of the title.
    Then do the sort on the helper column.

    Steve


    "kcg727" <[email protected]> wrote in message
    news:[email protected]...
    > I'm trying to sort a list in Excel and one of the columns is a list of
    > titles. When I sort I want it to ignore the words "The" and "A" at the
    > beginning. (for example, I want "The Piano" to be with the Ps not the
    > Ts).
    >
    > I'm about to throw my computer across the room because I can't figure it
    > out
    > and can't find anything in the Help sections that gives me the right
    > answer.
    > I'm not the most savvy with Excel, but this really shouldn't be that hard.
    > Help me.




+ 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