Need Macro for List of Book Titles

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!

Glad it worked out okay.

Regards

Bob


"Edye" <Edye@discussions.microsoft.com> wrote in message
news:41C7D28D-FB6E-4536-9053-F3FB8DE16DED@microsoft.com...
> Perfect! Thanks for your time. A few minutes of yours saved me many
hours
> of mine. You're the greatest!
>
> "Bob Phillips" wrote:
>
> > Wrap-around Edye. Try this
> >
> > 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:50D074FB-8499-4269-8574-CAEBA8C75299@microsoft.com...
> > > I get Comple Error: Sub or Function not defined. It highlights the
last
> > > "the" (w/o quotes).
> > >
> > > "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(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 t
he
> > 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!
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Perfect! Thanks for your time. A few minutes of yours saved me many hours
of mine. You're the greatest!

"Bob Phillips" wrote:

> Wrap-around Edye. Try this
>
> 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:50D074FB-8499-4269-8574-CAEBA8C75299@microsoft.com...
> > I get Comple Error: Sub or Function not defined. It highlights the last
> > "the" (w/o quotes).
> >
> > "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(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!
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Wrap-around Edye. Try this

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:50D074FB-8499-4269-8574-CAEBA8C75299@microsoft.com...
> I get Comple Error: Sub or Function not defined. It highlights the last
> "the" (w/o quotes).
>
> "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(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!
> > > >
> > > >
> > > >
> >
> >
> >

I get Comple Error: Sub or Function not defined. It highlights the last
"the" (w/o quotes).

"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(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!
> > >
> > >
> > >
>
>
>

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!
> >
> >
> >

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!
>
>
>

That will work, just assuming every book title has "The " at the beginning.
If a book title doesn't, for example "Gone with the wind" it will output "
with the wind, The"

--
David Billigmeier


"Brian Synowiec" wrote:

> How about this:
> Assuming your book names start in A1, and the titles are less than 100
> charecters (increase the 100 in the formula if they are longer);put this in
> B1 and copy down:
>
> =MID(A1,4,100)&", The"
>
> Brian
>
>
> "Edye" wrote:
>
> > 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!

Worked perfectly. THANK YOU for saving me days of work!!!

"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!
>
>
>

How about this:
Assuming your book names start in A1, and the titles are less than 100
charecters (increase the 100 in the formula if they are longer);put this in
B1 and copy down:

=MID(A1,4,100)&", The"

Brian


"Edye" wrote:

> 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!

You don't need vba, assuming all of the book title formats are the same, i.e.
"The " is the first 4 characters of the title (including the space) you can
use the following formula and just copy down to the end of your list:

=IF(LEFT(A1,4)="the ",CONCATENATE(RIGHT(A1,LEN(A1)-4),", The"),A1)

--
David Billigmeier


"Edye" wrote:

> 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!

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!

Wrap-around Edye. Try this

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:50D074FB-8499-4269-8574-CAEBA8C75299@microsoft.com...
> I get Comple Error: Sub or Function not defined. It highlights the last
> "the" (w/o quotes).
>
> "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(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!
> > > >
> > > >
> > > >
> >
> >
> >

You don't need vba, assuming all of the book title formats are the same, i.e.
"The " is the first 4 characters of the title (including the space) you can
use the following formula and just copy down to the end of your list:

=IF(LEFT(A1,4)="the ",CONCATENATE(RIGHT(A1,LEN(A1)-4),", The"),A1)

--
David Billigmeier


"Edye" wrote:

> 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!

I get Comple Error: Sub or Function not defined. It highlights the last
"the" (w/o quotes).

"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(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!
> > >
> > >
> > >
>
>
>

Perfect! Thanks for your time. A few minutes of yours saved me many hours
of mine. You're the greatest!

"Bob Phillips" wrote:

> Wrap-around Edye. Try this
>
> 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:50D074FB-8499-4269-8574-CAEBA8C75299@microsoft.com...
> > I get Comple Error: Sub or Function not defined. It highlights the last
> > "the" (w/o quotes).
> >
> > "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(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!
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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!
> >
> >
> >

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!
>
>
>

That will work, just assuming every book title has "The " at the beginning.
If a book title doesn't, for example "Gone with the wind" it will output "
with the wind, The"

--
David Billigmeier


"Brian Synowiec" wrote:

> How about this:
> Assuming your book names start in A1, and the titles are less than 100
> charecters (increase the 100 in the formula if they are longer);put this in
> B1 and copy down:
>
> =MID(A1,4,100)&", The"
>
> Brian
>
>
> "Edye" wrote:
>
> > 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!

How about this:
Assuming your book names start in A1, and the titles are less than 100
charecters (increase the 100 in the formula if they are longer);put this in
B1 and copy down:

=MID(A1,4,100)&", The"

Brian


"Edye" wrote:

> 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!

Glad it worked out okay.

Regards

Bob


"Edye" <Edye@discussions.microsoft.com> wrote in message
news:41C7D28D-FB6E-4536-9053-F3FB8DE16DED@microsoft.com...
> Perfect! Thanks for your time. A few minutes of yours saved me many
hours
> of mine. You're the greatest!
>
> "Bob Phillips" wrote:
>
> > Wrap-around Edye. Try this
> >
> > 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:50D074FB-8499-4269-8574-CAEBA8C75299@microsoft.com...
> > > I get Comple Error: Sub or Function not defined. It highlights the
last
> > > "the" (w/o quotes).
> > >
> > > "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(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 t
he
> > 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!
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Worked perfectly. THANK YOU for saving me days of work!!!

"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!
>
>
>

Perfect! Thanks for your time. A few minutes of yours saved me many hours
of mine. You're the greatest!

"Bob Phillips" wrote:

> Wrap-around Edye. Try this
>
> 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:50D074FB-8499-4269-8574-CAEBA8C75299@microsoft.com...
> > I get Comple Error: Sub or Function not defined. It highlights the last
> > "the" (w/o quotes).
> >
> > "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(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!
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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!

Wrap-around Edye. Try this

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:50D074FB-8499-4269-8574-CAEBA8C75299@microsoft.com...
> I get Comple Error: Sub or Function not defined. It highlights the last
> "the" (w/o quotes).
>
> "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(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!
> > > >
> > > >
> > > >
> >
> >
> >

Glad it worked out okay.

Regards

Bob


"Edye" <Edye@discussions.microsoft.com> wrote in message
news:41C7D28D-FB6E-4536-9053-F3FB8DE16DED@microsoft.com...
> Perfect! Thanks for your time. A few minutes of yours saved me many
hours
> of mine. You're the greatest!
>
> "Bob Phillips" wrote:
>
> > Wrap-around Edye. Try this
> >
> > 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:50D074FB-8499-4269-8574-CAEBA8C75299@microsoft.com...
> > > I get Comple Error: Sub or Function not defined. It highlights the
last
> > > "the" (w/o quotes).
> > >
> > > "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(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 t
he
> > 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!
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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!

I get Comple Error: Sub or Function not defined. It highlights the last
"the" (w/o quotes).

"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(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!
> > >
> > >
> > >
>
>
>

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!
> >
> >
> >

You don't need vba, assuming all of the book title formats are the same, i.e.
"The " is the first 4 characters of the title (including the space) you can
use the following formula and just copy down to the end of your list:

=IF(LEFT(A1,4)="the ",CONCATENATE(RIGHT(A1,LEN(A1)-4),", The"),A1)

--
David Billigmeier


"Edye" wrote:

> 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!

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!
>
>
>

That will work, just assuming every book title has "The " at the beginning.
If a book title doesn't, for example "Gone with the wind" it will output "
with the wind, The"

--
David Billigmeier


"Brian Synowiec" wrote:

> How about this:
> Assuming your book names start in A1, and the titles are less than 100
> charecters (increase the 100 in the formula if they are longer);put this in
> B1 and copy down:
>
> =MID(A1,4,100)&", The"
>
> Brian
>
>
> "Edye" wrote:
>
> > 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!

Worked perfectly. THANK YOU for saving me days of work!!!

"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!
>
>
>

How about this:
Assuming your book names start in A1, and the titles are less than 100
charecters (increase the 100 in the formula if they are longer);put this in
B1 and copy down:

=MID(A1,4,100)&", The"

Brian


"Edye" wrote:

> 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!