Forum Discussion

kbenning's avatar
kbenning
Copper Contributor
Feb 15, 2023

How do I remove the leading "The" from a list of books titles, but not any other "the"?

I need to alphabetize a column of book titles. I want the titles that start with "The" alphabetized by the next word in the title. Example: "The Big Sleep" would be alphabetized by "Big Sleep." I can...
excel
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Feb 15, 2023

kbenning 

Select the names and run the following macro:

Sub MoveThe()
    Dim cel As Range
    Dim adr As String
    Set cel = Selection.Find(What:="the *", LookAt:=xlWhole, MatchCase:=False)
    If Not cel Is Nothing Then
        Application.ScreenUpdating = False
        adr = cel.Address
        Do
            cel.Value = Mid(cel.Value, 4) & ", " & Left(cel.Value, 3)
            Set cel = Selection.Find(What:="the *", After:=cel, LookAt:=xlWhole, MatchCase:=False)
            If cel Is Nothing Then Exit Sub
        Loop Until cel.Address = adr
        Application.ScreenUpdating = True
    End If
End Sub

Resources