CODEWITHSUNDEEP
vbaexcel
user2901871
user2901871

Reputation: 219

VB code to remove special character in a column

I am having a column which contains integer values with two special character "," and "_". I am trying to remove these character for example 1,10_2,2_3,3 should be like 1102233. Thanks in advance for your suggestions.

Upvotes: 0

Views: 556

Answers (1)

e_jalbert
e_jalbert

Reputation: 36

this function isn't foolproof but it is a good start. 

Function trim(aStringToTrim As String, aElementToTrinm() As Variant) As String
Dim elementToTrim As Integer
Dim IndexInString As Integer

For elementToTrim = LBound(aElementToTrinm) To UBound(aElementToTrinm)
    IndexInString = InStr(aStringToTrim, aElementToTrinm(elementToTrim))

    Do While IndexInString > 0
        aStringToTrim = Left(aStringToTrim, IndexInString - 1) & Right(aStringToTrim, Len(aStringToTrim) - IndexInString - Len(aElementToTrinm(elementToTrim)) + 1)

        IndexInString = InStr(aStringToTrim, aElementToTrinm(elementToTrim))
    Loop
Next

End Function

It can be use like this:

Sub main()
    Dim myString As String
    Dim caracterstoRemove As Variant

    caracterstoRemove = Array(",", ".")

    myString = "This, is. a, string, with. caracters to remove."

    myString = trim(myString, caracterstoRemove)

End Sub

Upvotes: 1

Related Questions