Sub BookTitles()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
Cells(i, "A").Value = Right(Trim(Cells(i, "A").Value), _
Len(Cells(i, "A").Value) - 4) & ",
The"
End If
Next i

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Edye" <Edye@discussions.microsoft.com> wrote in message
news:2D874923-5268-46CE-A145-CC3FF6C92473@microsoft.com...
> One more little thing I noticed. There's a space b/w the end of the title
> and the ", The". How do I edit the macro to remove that space?
>
> "Bob Phillips" wrote:
>
> >
> > Sub BookTitles()
> > Dim iLastRow As Long
> > Dim i As Long
> >
> > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > For i = 1 To iLastRow
> > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
> > Cells(i, "A").Value = Right(Cells(i, "A").Value, Len( _
> > Cells(i, "A").Value) - 4) & ",
The"
> > End If
> > Next i
> >
> > End Sub
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Edye" <Edye@discussions.microsoft.com> wrote in message
> > news:85DF4268-CB44-495E-9830-AD7FC82BE4C1@microsoft.com...
> > > Excel 2003
> > > I have a list of books. About 700 contain a "The" at the beginning.
I
> > need
> > > to add ", The" (without quotes) to the end of each one of those book
> > titles
> > > and remove the "The" at the beginning of the title. I recorded a
Macro
> > for
> > > it, but each time I use the Macro, it replaces the other cells with
the
> > title
> > > of the one which I recorded. Thanks!
> >
> >
> >