ACC: How to Strip Specific Characters from a String

MORE INFORMATION

1. Create a new module and type the following line in the Declarations section if it's not already there:

   Option Explicit

2. Type the following procedure:

         ' --------------------------------------------------
         ' Function StripString()
         '
         ' Returns a string minus a set of specified chars.
         ' --------------------------------------------------
         Function StripString(MyStr As Variant) As Variant
            On Error GoTo StripStringError
   
            Dim strChar As String, strHoldString As String
            Dim i As Integer
   
            ' Exit if the passed value is null.
            If IsNull(MyStr) Then Exit Function
   
            ' Exit if the passed value is not a string.
            If VarType(MyStr) <> 8 Then Exit Function
   
            ' Check each value for invalid characters.
            For i = 1 To Len(MyStr)
               strChar = Mid$(MyStr, i, 1)
               Select Case strChar
                  Case ".", "#", ",", "-"
                     ' Do nothing
                  Case Else
                     strHoldString = strHoldString & strChar
               End Select
            Next i
   
            ' Pass back corrected string.
            StripString = strHoldString
   
         StripStringEnd:
            Exit Function
   
         StripStringError:
            MsgBox Error$
            Resume StripStringEnd
         End Function
   						

3. To test this function, type the following line in the Debug window (or Immediate window in versions 1.x or 2.0), and then press ENTER:

   ?StripString("Test.#,-")

   Note that the function prints the word "Test" in the Debug window. The invalid characters (".#,-") are successfully stripped from the test string.