Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Moving characters in a cell (2003 SP2)

    I have a worksheet that tracks movie titles. One column contains the title, and that is the column I want to be the primary sort. However, the titles are listed strictly as the movies are known, including, where applicable, a definite or indefinite article. I want to sort ignoring the article. I wish either to move the article to the end of the title and add a comma; thus "A Few Good Men" becomes "Few Good Men, A", or just sort ignoring the article altogether. I would guess that this would need a macro or some VB code, but I don't know how to do this. Can anyone help?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving characters in a cell (2003 SP2)

    Say that the movie titles are in column A, starting in A1.
    Enter the following awkward formula in B1:
    <code>
    =IF(LEFT(A1,4)="The ",MID(A1,5,1000)&", "&LEFT(A1,4),IF(LEFT(A1,3)="An ",MID(A1,4,1000)&", "&LEFT(A1,3),IF(LEFT(A1,2)="A ",MID(A1,3,1000)&", "&LEFT(A1,2),A1)))
    </code>
    and fill down as far as needed. Select B1, then click the Sort Ascending button. Colunmn A will be sorted together with column B. You can hide column B if you want.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving characters in a cell (2003 SP2)

    Thanks, Hans. That worked. I hate those multiple embedded IF statements; I always miss a quote or get the parentheses mangled.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving characters in a cell (2003 SP2)

    If you prefer a VBA solution, you can use this custom function:
    <code>
    Public Function Name4Sort(strVal As String) As String
    If LCase(Left(strVal, 2)) = "a " Then
    Name4Sort = Mid(strVal, 3) & ", " & Left(strVal, 2)
    ElseIf LCase(Left(strVal, 3)) = "an " Then
    Name4Sort = Mid(strVal, 4) & ", " & Left(strVal, 3)
    ElseIf LCase(Left(strVal, 4)) = "the " Then
    Name4Sort = Mid(strVal, 5) & ", " & Left(strVal, 4)
    Else
    Name4Sort = strVal
    End If
    End Function
    </code>
    With a name in A1, enter =Name4Sort(A1) in B1 and fill down as far as needed. If you put the function in your Personal.xls, use
    <code>=Personal.xls!Name4Sort(A1)</code>

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving characters in a cell (2003 SP2)

    Thanks, Hans. That works too!

    I have another question but its not related to this so I'm putting it in a new thread.

Posting Permissions

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