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

Help Studio
, ,
1

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)

2

Hi @ydash999,

  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

1 Like
3

  1. put below code in text file 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

4

(From r In DT_Data.AsEnumerable Let ra = r.ItemArray.Select(Function (x) x.ToString.Trim.Replace("your target ",“ ”)).toArray() Select DT_Data.Rows.Add(ra)).CopyToDataTable()

try this after read excelfile

1 Like
5

how can i replace all the characters ,placing those in “your target” ?i can only give one character right?

6

@ydash999

May i know from where you are getting this excel data

7

I am just giving a scenario, if these kinds of data come, and we have to clean them and feed to queue, then how to do

8

yesh you can give which one you want to replace

example for if you want to replace “@” with “#” , @ is your target value and # will be destination

1 Like
9

in this one code can i replace all the unwanted characters with “”?

10

Hey @ydash999

Read the Excel data into a table.

Replace the unwanted symbols with empty string for each row.

Write the table back to the Excel.

Hope this helps.

Thanks
#nK

11

yeah it will

12

how to use replace method within for each?

13

in “target” i can only give one character like # or @ or any other right? how to replace more than one character with"" within “target”?

14

try this
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 …
1 Like
15

Hello @ydash999

you can do as below.

All the unwanted symbols are added in the below regex. If you want to add more symbols you can directly add it in the expression.

System.Text.RegularExpressions.Regex.Replace(Str, “([`…:>(!“”/-])”, “”)

image
image489×702 19.1 KB

image
image
ExcelAutomationDemo.zip (77.1 KB)

1 Like
16

you can give whatever character,number, string or anything

17

thankss bro!seems good solution to me

1 Like
18

thankss a lot !

19

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.