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't simply remove the first four characters in all the cells in the column because not all the titles start with "The." Also, I can't simply do a find/replace with the word "The" because "the" occurs in other parts of titles:

Murder on the Orient Express and other Hercule Poirot mysteries
Readings on the short stories of Edgar Allan Poe.
Sherlock Holmes : the complete novels and stories
Shutter Island
The beekeeper's apprentice, or, On the segregation of the queen
The big sleep
The Bourne supremacy.
The client.

What I'd REALLY like is to be able to remove the leading "The" AND place it at the end, like this:

big sleep, The

Any help is greatly appreciated!

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