Results 1 to 3 of 3
  1. #1
    rwyndham
    Guest

    sorting text with "the" and "a" ignored (2000)

    Hello:

    I realize that I should have used a true database, but... As somewhat of a novice I have created a 15,000 line book catalogue in Excel. Headings are author, title, etc. I mostly sort by author and title. Is there any way I can get Excel to ignore "the," "a," and "an" as the first word of the book title when sorting?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sorting text with "the" and "a" ignored (2000)

    You could put the formula below into a an empty column and fill it down, then sort on that column.

    <pre>=IF(LEFT(LOWER(A1),4)="the ",RIGHT(A1,LEN(A1)-4),IF(LEFT(LOWER(A1),3)="an ",RIGHT(A1,LEN(A1)-3),IF(LEFT(LOWER(A1),2)="a ",RIGHT(A1,LEN(A1)-2),A1)))
    </pre>


    That will not handle something like "And The Band Played On" however. If you want to handle that, and or be able to add other words like "For", then you will need a VBA procedure to create the sort column.
    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sorting text with "the" and "a" ignored (2000)

    Here is a User Defined Function that will remove more than one word from the front of the title (To Solve "And The Band Played On"), and you also pass it a list of the words you want removed.

    <pre>Public Function GetShortName(strString As String, oTrivialWords As Range) As String
    Dim oCurTrivial As Range, strWk As String
    Dim strWd As String, iLen As Integer
    Dim bDone As Boolean
    strWk = strString
    bDone = False
    Do While (Len(strWk) > 0 And bDone = False)
    bDone = True
    For Each oCurTrivial In oTrivialWords
    strWd = LCase(Trim(oCurTrivial.Value) & " ")
    iLen = Len(strWd)
    If Len(strWk) >= iLen Then
    If Left(LCase(strWk), iLen) = strWd Then
    strWk = Trim(Right(strWk, Len(strWk) - iLen))
    bDone = False
    Exit For
    End If
    End If
    Next oCurTrivial
    Loop
    GetShortName = strWk
    End Function
    </pre>


    If your titles are in column A, and you have a list of the words you want removed in E1 to E9, then you could enter the following in the top cell of an empty column and then fill it down:

    <pre>=GetShortName(A1,$E$1:$E:$9)
    </pre>


    Then sort on that column.
    Legare Coleman

Posting Permissions

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