How do I clear the unwanted characters from my rows in excel sheet?

  1. put below code in text filee e.g. vba.txt

  2. in excel application scope call the vba function
    image

Function removeSpecial()
    Dim r As Range
    Set r = ActiveSheet.UsedRange
    Dim cellRange As Range
    For Each cellRange In r
        Debug.Print "r"
        cellRange.Value = FindReplaceRegex(cellRange, "([\" & Chr(34) & "<>\(\):\!\`\-\.])", "")
    Next cellRange

    ActiveWorkbook.Save
    
End Function 


Function FindReplaceRegex(rng As Range, reg_exp As String, replace As String)
   'Dim regex As Object
    Set myRegExp = CreateObject("VBScript.RegExp")
    'Set myRegExp = New RegExp
    myRegExp.IgnoreCase = False
    myRegExp.Global = True
    myRegExp.Pattern = reg_exp
    
    FindReplaceRegex = myRegExp.replace(rng.Value, replace)
End Function

result

image

1 Like