title: VBA: How to remove non-printable characters from data
body: <p>I need to remove programmatically non printable characters such as:</p>
  
  <p>tabs - char(9)
  line breaks - char(10)
  carriage return - char(13)
  data link escape - char(16)</p>
  
  <p>I started a generic function that will be called from the lost_focus event of the ms access form field.</p>
  
  <p>I have not figured out how to identify when the string contains the unwanted characters.</p>
  
  <pre><code>    Function RemoveNonPrintableCharacters(ByVal TextData) As String
  
          Dim dirtyString As String
          Dim cleanString As String
          Dim iPosition As Integer
  
          If IsNull(TextData) Then
              Exit Function
          End If
  
          dirtyString = TextData
          cleanString = ""
  
          For iPosition = 1 To Len(dirtyString)
              Select Case Asc(Mid(dirtyString, iPosition, 1))
                  Case 9    ' Char(9)
                  Case 10   ' Char(10)
                  Case 13   ' Char(13)
                  Case 16   ' Char(16)
                  Case Else ' Add character to clean field.
                      cleanString = cleanString &amp; Mid(dirtyString, iPosition, 1)
              End Select
          Next
  
          RemoveNonPrintableCharacters = cleanString
  
      End Function
  </code></pre>
  
  <p>These are 2 strings I have been using whilst testing:</p>
  
  <pre><code>This line,    has       multiple,     tabs       that   need to be removed
  
  
  This line, has multiple,     
  line
  breaks
  that
  need to be removed
  
  This line,    has       multiple,     tabs       that   need to be removed
  And
  Also contains
  multiple,     
  line
  breaks
  that
  need to be  removed
  </code></pre>