+ Reply to Thread
Results 1 to 4 of 4

Article Relabelling

  1. #1
    Registered User
    Join Date
    08-01-2006
    Posts
    27

    Article Relabelling

    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!

  2. #2
    Die_Another_Day
    Guest

    Re: Article Relabelling

    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



  3. #3
    Die_Another_Day
    Guest

    Re: Article Relabelling

    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



  4. #4
    Peter Perception
    Guest

    RE: Article Relabelling

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1