How to search and list (filter) and show all result in Excel

[son.dc]

Hi everyone, i have excel file with many values. Now i want to find all values which contain special character ( like "@;#;$…) and show off for user.
For example, normally, user will use filter many times to get results as picture

Next step, user will correct them then save excel file.
I did it with datatable but how to run on excel?
Tks

[Gokul001 (Gokul Balaji)]

H @son.dc

Can you share the sample excel file?

[supermanPunch (Arpan)]

@son.dc

i have excel file with many values. Now i want to find all values which contain special character ( like "@;#;$…) and show off for user.

Hi @son.dc ,

We could Filter the Datatable to extract the rows that contains the special characters using the below Expression :

DT.AsEnumerable.Where(Function(x){"@","#","$"}.Any(Function(y)x("Barcode").ToString.Contains(y))).CopyToDatatable

A Handling might need to be performed for Direct CopyToDatatable, You could check the below post on handling it :

[FirstAid] Handling of The source contains no DataRows exception

This FirstAid Tutorial will describe how a the source contains no DataRows EXCEPTION can be handled. Introduction Let’s have a look at the following filter data table scenario: Name CCode Tom US Charlotte FR Seema IN Jean FR Assignment: Filter all rows on a particular Country Code like FR, UK, ES, UK… Ensure that all CCode data column values are trimmed Ensure that the Filter check is case insensitive we can implement it e.g. with the help of a LINQ statement: dtData.As…

@son.dc

I did it with datatable but how to run on excel?

Could you elaborate more on what is meant by run on Excel ?

[son.dc]

Yes,
The excel file was export from handy scanner. So user will find error values, and correct it, then save.
Barcode_22112022110017.xlsx (29.6 KB)
i use Filter data table activity (because it can do with more than 2 conditions), it show result what i expect, but how to user can fix it directly and save to excel file after edit