get rid of white space no matter where in the string

Question

I am trying to remove white space no matter what position it is in -- within a string. I have to write the code in VBA. Below is what I have written so far. When I use chr(32) or space(1), VBA seems to throw a fit and not like what I have written. In other words, I want to pass a string to the cleanFunction: FB 10-0073 and have it return: FB100073. Notice, no white space.

Const SpecialCharacters As String = "!,@,#,$,%,^,&,*,(,),{,[,],},?,-" 'chr(32),chr(95),chr(160),chr(47),chr(45)"  'modify as needed
Private Const EXPC As String = "EXP_C"

Public Function cleanString(ByVal text As String) As String
    Dim newString As String
    Dim char As Variant
    For Each char In Split(SpecialCharacters, ",")
        newString = Trim(Replace(text, char, ""))
    Next
    cleanString = newString
End Function

Answer 1

Get Rid Off Characters

1.5 Problems

• You don't have the space character in the variant (probably a typo).
• You are doing the trim-replace always on the initial text, so what ever character is last in the variant ("-" in your case) will be replaced, the others will not.

The Code

Option Explicit

Function cleanString(ByVal CleanText As String) As String
    Const SpecialCharacters As String = _
            "!,@,#,$,%,^,&,*,(,),{,[,],},?,-,_,/, " ' chr(160)
    Dim newString As String
    Dim char As Variant
    Dim i As Integer
    newString = CleanText
    char = Split(SpecialCharacters, ",")
    For i = 0 To UBound(char)
        newString = Replace(newString, char(i), "")
    Next
    cleanString = newString
End Function