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" wrote in message
...
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!