function to replace multiple characters..

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim Burke in Novi - 23 Apr 2008 22:02 GMT

I want to take a string and replace any special characters like ()%$#- with a
space. For example, if the string is '(ABC # 123 $)' I want to return ' ABC
123 '. Is there a function to do this? Replace only searches for one
specific string, but I need to say replace any single special character with
a space. I know I can write my own function, but it seems that there would be
one that does this. In the ancient days of mainframe programming I seem to
remember a function called TRANSLATE that did this. Any help is appreciated.
Thanks

Jim B

Reply to this Message

Marshall Barton - 23 Apr 2008 22:34 GMT

>I want to take a string and replace any special characters like ()%$#- with a
>space. For example, if the string is '(ABC # 123 $)' I want to return ' ABC
[quoted text clipped - 3 lines]
>one that does this. In the ancient days of mainframe programming I seem to
>remember a function called TRANSLATE that did this. Any help is appreciated.

That Translate instruction was a very elaborate affair that
I have not seen since. You can easily create your own
function using something like this:

Function ClearSpecials(strData As String) As String
Dim k As Integer
    ClearSpecials = strData
    For k = 1 To Len(ClearSpecials)
        If Not Mid(ClearSpecials,k,1) Like "[a-z 0-9]" Then
            ClearSpecials = Replace(ClearSpecials, _
                                                        Mid(ClearSpecials,k,1), " ")
        Else
            Exit For
        End If
    Next k

Signature

Marsh
MVP [MS Access]

Reply to this Message

Jim Burke in Novi - 23 Apr 2008 22:57 GMT

I knew I could write my own fairly simply, but I did forget about being able
to use that Like clause. That simplifies it some. The Translate I used in PL1
was fairly straightforward, I think it did exactly what I'm looking for. I
think there may have been one in SAS that was a whole different animal.
Thanks for the response.

> >I want to take a string and replace any special characters like ()%$#- with a
> >space. For example, if the string is '(ABC # 123 $)' I want to return ' ABC
[quoted text clipped - 19 lines]
> End If
> Next k

Reply to this Message

Marshall Barton - 23 Apr 2008 23:20 GMT

Actually, Translate was a machine instruction in the IBM360
(and follow on) series of main frames so naturally IBM added
a function to PL1 that utilized it ;-) However, the Basic
language was (tongue in cheek) a counter reaction to the
committee designed languages like PL1 and Cobol ;-\

And I agree that when needed, it was very handy.

Signature

Marsh
MVP [MS Access]

>I knew I could write my own fairly simply, but I did forget about being able
>to use that Like clause. That simplifies it some. The Translate I used in PL1
[quoted text clipped - 24 lines]
>> End If
>> Next k

Reply to this Message