excelspecial-characters

Remove special characters from string Excel


I used following VBA function code to remove special characters from a string, keep only numbers. But it doesn't work with the decimal numbers. Here are examples: It works for:

     960     ==> 960

But it doesn’t work for:

     0.002    ==> 0002

(the result must be 0.002) Could you resolve it?

Function RemoveSpecialChar(str As String) As String
With CreateObject("Vbscript.regexp")
.Global = True
.Pattern = "(?:\W|_)+"
RemoveSpecialChar = .Replace(str, "")
End With
End Function

Solution

  • Function KeepNumericAndDot(str As String) As String
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "[^0-9\.\-]+"
            KeepNumericAndDot = .Replace(str, "")
        End With
    End Function