Remove symbols from String

**aqpham1** · 12-15-2011, 03:33 AM

Does anyone know a VBA code to remove symbols from strings? I'm importing a file that comprises of Alphabets and Numeric characters but sometimes has all different types of symbols in random places. For example, it might be TES1244##56@@ but all I want is TES124456. The problem is the symbols appear in random locations so I thought about editing this code below but the problem is it removes only numeric numbers which I want to keep. Is there a way the I can make the for loop loop from 1 to 256, convert index i to an ascii character, then use Application.Substitude(TMP,(ascii index i),"")? Thanks

----------------------------------------------------------

Please Login or Register  to view this content.

-----------------------------------------------------------

**huuthang_bd** · 12-15-2011, 03:53 AM

You can try with this code:

PHP Code:


Function RemoveSymbol(str As String) As String
With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[^0-9A-z]"
    RemoveSymbol = .Replace(str, "")
End With
End Function

My English is very bad. I'm sorry if you feel uncomfortable.