0

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.

Community
  • 1
  • 1
user2901871
  • 219
  • 5
  • 6
  • 10
  • This question has been answered here: http://stackoverflow.com/questions/15723672/how-to-remove-all-non-alphanumeric-characters-from-a-string-except-period-and-sp – hnk Jul 15 '14 at 09:37

1 Answers1

1

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
e_jalbert
  • 36
  • 3