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

[ydash999 (YashdevUiPath)]

How to delete the unwanted characters like !,`,-,. etc. from my excel rows and obtain a clean sheet?here i have attached my test excel
exceldt.xlsx (9.4 KB)

[muhammedyuzuak]

Hi @ydash999,

I think you opened this thread twice. You can remove one.

1. Read Excel with the readragen activity.

2. Convert the datatable to a string with outdatatable.

3. Clean up the entire result string with regex.

4. You can convert it back to arraylist and turn it into a datatable.

Regards,
MY

[ydash999 (YashdevUiPath)]

Is there any other way?

[jack.chan]

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

2. in excel application scope call the vba function
   

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

[muhammedyuzuak]

@jack.chan shared a better solution but you will need to edit this code for possible change requests.

[ydash999 (YashdevUiPath)]

thankss bro! but it is getting too lengthy and much codes. is there any other way?

[jack.chan]

this does the same thing but without any VBA code
d.xaml (10.5 KB)

if you dont use regex you can also use multiple assign statements to replace the special characters e.g.

1. CurrentRow(col.ColumnName) = CurrentRow(col.ColumnName).toString.replace(“<”, “”)
2. CurrentRow(col.ColumnName) = CurrentRow(col.ColumnName).toString.replace(“>”, “”)
   and so on …