Removing specific characters from a text string

[Mr. Perry]

Hello,

Is there any way to remove specific characters from a
text string using a query?

I don't want to use the find and replace feature because
the process needs to be automated.

One of the problems is the phone number data field. This
information can come with many characters that I don't
want in the data, and since I'll be working with
thousands of records at a time I need to be able to
automate this procedure. Some of the illegal chars would
be for example, "\" or "/" or "," or "." or "(" or ")". I
need to remove any instance of these characters from the
string without deleting all of the data that isn't one of
these illegal characters.

Please keep in mind I need to work in text format with
these phone numbers as well.

Sincerely,

~Mr. Perry

 

[Allen Browne]

The function below returns the phase you pass in, stripped of any of the
characters in the 2nd argument.

Use it in an Update query to clean up your existing data, or in the
AfterUpdate event of the control to remove the characters before they are
saved.

Function Strip(vPhrase, sBadChars As String)
' Purpose: remove any of the characters in sBadChars from vPhrase
Dim sPhrase As String
Dim i As Integer

If IsNull(vPhrase) Or IsEmpty(vPhrase) Or Len(sBadChars) = 0 Then
Strip = vPhrase
Else
sPhrase = vPhrase
i = 1
Do Until i > Len(sPhrase)
Do Until InStr(sBadChars, Mid$(sPhrase, i, 1)) = 0 Or i >
Len(sPhrase)
sPhrase = Left$(sPhrase, i - 1) & Mid$(sPhrase, i + 1)
Loop
i = i + 1
Loop
Strip = sPhrase
End If
End Function

 

[Guest]

Allen,

Thank you so much!

-----Original Message-----
The function below returns the phase you pass in, stripped of any of the
characters in the 2nd argument.

Use it in an Update query to clean up your existing data, or in the
AfterUpdate event of the control to remove the characters before they are
saved.

Function Strip(vPhrase, sBadChars As String)
' Purpose: remove any of the characters in sBadChars from vPhrase
Dim sPhrase As String
Dim i As Integer

If IsNull(vPhrase) Or IsEmpty(vPhrase) Or Len (sBadChars) = 0 Then
Strip = vPhrase
Else
sPhrase = vPhrase
i = 1
Do Until i > Len(sPhrase)
Do Until InStr(sBadChars, Mid$(sPhrase, i, 1)) = 0 Or i >
Len(sPhrase)
sPhrase = Left$(sPhrase, i - 1) & Mid$(sPhrase, i + 1)
Loop
i = i + 1
Loop
Strip = sPhrase
End If
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.




.