Text convert Code please

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Graham Feeley

    #1

    Text convert Code please

    Hi I have data resembling like this
    Clearing, The
    Italian Job, The
    Final Cut, The
    Manson Family, The
    I would like some code to convert the data to

    The Clearing
    The Italian Job
    The Final Cut
    The Manson Family
    Can Someone please help me??
    Regards
    Graham


  • Douglas J. Steele

    #2
    Re: Text convert Code please

    Assuming the text to be transformed is stored in OriginalText, something
    like the following will transform it as desired:

    Trim(Mid(Origin alText, InStr(OriginalT ext, ",") + 1)) & " " &
    Left(OriginalTe xt, InStr(OriginalT ext, ",") - 1)

    Of course, that'll fail if there isn't a comma in the text, but
    unfortunately, due to how the IIf function works, you can't simply use:

    IIf(InStr(Origi nalText, ",") = 0, OriginalText, Trim(Mid(Origin alText,
    InStr(OriginalT ext, ",") + 1)) & " " & Left(OriginalTe xt,
    InStr(OriginalT ext, ",") - 1))

    you'll have to write a function like:

    Function FixText(Origina lText As String) As String

    If InStr(OriginalT ext, ",") = 0 Then
    FixText = OriginalText
    Else
    FixText = Trim(Mid(Origin alText, InStr(OriginalT ext, ",") + 1)) & " "
    & Left(OriginalTe xt, InStr(OriginalT ext, ",") - 1)
    End If

    End Function

    --
    Doug Steele, Microsoft Access MVP

    (no e-mails, please!)



    "Graham Feeley" <grahamjfeeley@ optusnet.com.au > wrote in message
    news:42ff1953$0 $30086$afc38c87 @news.optusnet. com.au...[color=blue]
    > Hi I have data resembling like this
    > Clearing, The
    > Italian Job, The
    > Final Cut, The
    > Manson Family, The
    > I would like some code to convert the data to
    >
    > The Clearing
    > The Italian Job
    > The Final Cut
    > The Manson Family
    > Can Someone please help me??
    > Regards
    > Graham
    >
    >[/color]


    Comment

    • Graham Feeley

      #3
      Re: Text convert Code please

      Thanks Douglas for a very instant reply, I am off to work now will try it
      tomorrow evening, will let u know.
      Really appreciate the help and time you guys out in here
      Regards
      Graham

      "Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message
      news:TpCdnafm7M 6ct2LfRVn-gw@rogers.com.. .[color=blue]
      > Assuming the text to be transformed is stored in OriginalText, something
      > like the following will transform it as desired:
      >
      > Trim(Mid(Origin alText, InStr(OriginalT ext, ",") + 1)) & " " &
      > Left(OriginalTe xt, InStr(OriginalT ext, ",") - 1)
      >
      > Of course, that'll fail if there isn't a comma in the text, but
      > unfortunately, due to how the IIf function works, you can't simply use:
      >
      > IIf(InStr(Origi nalText, ",") = 0, OriginalText, Trim(Mid(Origin alText,
      > InStr(OriginalT ext, ",") + 1)) & " " & Left(OriginalTe xt,
      > InStr(OriginalT ext, ",") - 1))
      >
      > you'll have to write a function like:
      >
      > Function FixText(Origina lText As String) As String
      >
      > If InStr(OriginalT ext, ",") = 0 Then
      > FixText = OriginalText
      > Else
      > FixText = Trim(Mid(Origin alText, InStr(OriginalT ext, ",") + 1)) & "[/color]
      "[color=blue]
      > & Left(OriginalTe xt, InStr(OriginalT ext, ",") - 1)
      > End If
      >
      > End Function
      >
      > --
      > Doug Steele, Microsoft Access MVP
      > http://I.Am/DougSteele
      > (no e-mails, please!)
      >
      >
      >
      > "Graham Feeley" <grahamjfeeley@ optusnet.com.au > wrote in message
      > news:42ff1953$0 $30086$afc38c87 @news.optusnet. com.au...[color=green]
      > > Hi I have data resembling like this
      > > Clearing, The
      > > Italian Job, The
      > > Final Cut, The
      > > Manson Family, The
      > > I would like some code to convert the data to
      > >
      > > The Clearing
      > > The Italian Job
      > > The Final Cut
      > > The Manson Family
      > > Can Someone please help me??
      > > Regards
      > > Graham
      > >
      > >[/color]
      >
      >[/color]


      Comment

      Working...