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

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!

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

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!

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

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

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

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

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

Glad it worked out okay.

Regards

Bob


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