function to replace multiple characters..

[Jim Burke in Novi]

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

 

[Marshall Barton]

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.

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

 

[Jim Burke in Novi]

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

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

 

[Marshall Barton]

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

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.

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