Feb 15 2023 03:01 PM
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!
Feb 15 2023 03:15 PM
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
Feb 15 2023 03:16 PM
=IF(LEFT(A1,3)="The",CONCATENATE(RIGHT(A1,LEN(A1)-4),", the"),A1)
You can try this formula.