Is there any way to take something like "The Break-up" and change it t
"Break-up, The"? The same applies to the article "a" and "an". Is ther
any way to automate the process so I do not have to go back and manuall
edit each entry? I'd appreciate any guidance

--
montreal177
-----------------------------------------------------------------------
montreal1775's Profile: http://www.excelforum.com/member.php...fo&userid=3699
View this thread: http://www.excelforum.com/showthread.php?threadid=56716

sub MoveArticles()
Const ColNum as Long = 1
Dim cnt as long
Dim Art as string
For cnt = 1 to Cells(Rows.Count, ColNum).End(xlup).Row
Art = Left(Cells(cnt,ColNum), Instr(1,Cells(cnt,ColNum)," ")-1)
If lCase(Art) = "the" or lCase(Art) = "an" or lCase(Art) = "a" Then
Cells(cnt,1) =
Right(Cells(cnt,ColNum),Len(Cells(cnt,ColNum))-Instr(1,Cells(cnt,ColNum),"
") _
& Art
End If
Next
End Sub

Change the const ColNum to the column number you want to change

HTH

Die_Another_Day
montreal1775 wrote:
Is there any way to take something like "The Break-up" and change it to
"Break-up, The"? The same applies to the article "a" and "an". Is there
any way to automate the process so I do not have to go back and manually
edit each entry? I'd appreciate any guidance!


--
montreal1775
------------------------------------------------------------------------
montreal1775's Profile: http://www.excelforum.com/member.php...o&userid=36994
View this thread: http://www.excelforum.com/showthread...hreadid=567163

Oops forgot the comma
This line:
Cells(cnt,1) = Right(Cells(cnt,ColNum), _
Len(Cells(cnt,ColNum))- _
Instr(1,Cells(cnt,ColNum)," ") & Art

Should Read like this:

Cells(cnt,1) = Right(Cells(cnt,ColNum), _
Len(Cells(cnt,ColNum))- _
Instr(1,Cells(cnt,ColNum)," ") & " , " & Art

Die_Another_Day

Die_Another_Day wrote:
sub MoveArticles()
Const ColNum as Long = 1
Dim cnt as long
Dim Art as string
For cnt = 1 to Cells(Rows.Count, ColNum).End(xlup).Row
Art = Left(Cells(cnt,ColNum), Instr(1,Cells(cnt,ColNum)," ")-1)
If lCase(Art) = "the" or lCase(Art) = "an" or lCase(Art) = "a" Then
Cells(cnt,1) =
Right(Cells(cnt,ColNum),Len(Cells(cnt,ColNum))-Instr(1,Cells(cnt,ColNum),"
") _
& Art
End If
Next
End Sub

Change the const ColNum to the column number you want to change

HTH

Die_Another_Day
montreal1775 wrote:
Is there any way to take something like "The Break-up" and change it to
"Break-up, The"? The same applies to the article "a" and "an". Is there
any way to automate the process so I do not have to go back and manually
edit each entry? I'd appreciate any guidance!


--
montreal1775
------------------------------------------------------------------------
montreal1775's Profile: http://www.excelforum.com/member.php...o&userid=36994
View this thread: http://www.excelforum.com/showthread...hreadid=567163

You dont need Visual Basic. This is what a cell formula can do:
COLUMN A COLUMN B
the break up break up, the
a few few, a
a few more few more, a
an adult adult, an
an awsome adult awsome adult, an
And this is how the formula in column B looks like
=IF(LEFT(A1;4)="the ";CONCATENATE(RIGHT(A1;LENGTE(A1)-4);",
";"the");IF(LEFT(A1;3)="AN ";CONCATENATE(RIGHT(A1;LENGTE(A1)-3);",
";"an");IF(LEFT(A1;2)="a ";CONCATENATE(RIGHT(A1;LENGTE(A1)-2);", ";"a"))))

"montreal1775" wrote:


Is there any way to take something like "The Break-up" and change it to
"Break-up, The"? The same applies to the article "a" and "an". Is there
any way to automate the process so I do not have to go back and manually
edit each entry? I'd appreciate any guidance!


--
montreal1775
------------------------------------------------------------------------
montreal1775's Profile: http://www.excelforum.com/member.php...o&userid=36994
View this thread: http://www.excelforum.com/showthread...hreadid=567163