Assuming the text to be transformed is stored in OriginalText, something
like the following will transform it as desired:
Trim(Mid(OriginalText, InStr(OriginalText, ",") + 1)) & " " &
Left(OriginalText, InStr(OriginalText, ",") - 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(OriginalText, ",") = 0, OriginalText, Trim(Mid(OriginalText,
InStr(OriginalText, ",") + 1)) & " " & Left(OriginalText,
InStr(OriginalText, ",") - 1))
you'll have to write a function like:
Function FixText(OriginalText As String) As String
If InStr(OriginalText, ",") = 0 Then
FixText = OriginalText
Else
FixText = Trim(Mid(OriginalText, InStr(OriginalText, ",") + 1)) & " "
& Left(OriginalText, InStr(OriginalText, ",") - 1)
End If
End Function
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Graham Feeley" <gr***********@optusnet.com.au> wrote in message
news:42***********************@news.optusnet.com.a u...
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
