Moving characters in a cell (2003 SP2)

Topic

New Reply

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?

Reply | Quote

Replies

Say that the movie titles are in column A, starting in A1.
Enter the following awkward formula in B1:

=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)))

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.

Reply | Quote

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

Reply | Quote

If you prefer a VBA solution, you can use this custom function:

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

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
=Personal.xls!Name4Sort(A1)

Reply | Quote

Thanks, Hans. That works too!

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

Reply | Quote