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

Help Studio
, ,
4

  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