CODEWITHSUNDEEP
vba
matt_codingtyro
matt_codingtyro

Reputation: 81

get rid of white space no matter where in the string

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

Upvotes: 2

Views: 118

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

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

Upvotes: 2

Related Questions