Filter Excel on multiple criteria blank rows and keep rows containing specific values

[1a2bc346dgjk7544ebjkkbvf (Mi uipath s)]

rule:

1. delete the row which has empty cell in column1
2. Delete the row which has only alphabets but keep the row which has single alphabet like J ,K , H etc
3. Keep the row which has M2345.CE pattern
4. Keep the row If numeric but not single digit. if only 1 , 5 then delete but if 460.648.55 then keep row
5. Keep rows which has alpha numeric values- CJ098L pr T12346W etc
   output.xlsx (8.5 KB)

I am able to delete blank rows and keep .CE rows but if apply multiple criteria it’snot working . It might need Linq or VBA or regex

[supermanPunch (Arpan)]

@1a2bc346dgjk7544ebjkkbvf

output.xlsx (8.5 KB)

Hi @1a2bc346dgjk7544ebjkkbvf ,

I believe you have provided us with the Input data, Could you also provide us with the Expected Output, so that we can confirm on the logic that we would require to apply.

[1a2bc346dgjk7544ebjkkbvf (Mi uipath s)]

output (1).xlsx (9.4 KB)
Thank you for reply!
Sheet 2 has output

[supermanPunch (Arpan)]

@1a2bc346dgjk7544ebjkkbvf ,

Maybe we could generalise with a Regex Expression for all the conditions mentioned :

OutputDT = DT.AsEnumerable.Where(Function(x)System.Text.RegularExpressions.Regex.IsMatch(x(0).ToString,"\b[A-Za-z]{1}\b|[A-Za-z]+[0-9]+|\d{2,}")).CopyToDatatable

Here, DT is the Input datatable and OutputDT is the Output datatable.

Could you try the above expression and let us know if it is working or not. Do try with different data and test it.

[1a2bc346dgjk7544ebjkkbvf (Mi uipath s)]

Hats off to you

It worked, Thanks a lot !!

[1a2bc346dgjk7544ebjkkbvf (Mi uipath s)]

@supermanPunch where can I learn such things ? I know simple regex to get Numeric value from pdf, etc, but not this types. Any source to update knowledge ?
TIA !

[supermanPunch (Arpan)]

@1a2bc346dgjk7544ebjkkbvf

where can I learn such things ?

@1a2bc346dgjk7544ebjkkbvf ,

We do have tutorials here :

Regex help tutorial MEGAPOST – Making your first Regex post, Reusable Regex Patterns, Regex Troubleshooting, Sample Workflow and more

THE PURPOSE OF THIS POST IS TO HELP NEW REGEX USERS If you need help with Regex be sure to provide in your post, a Sample, the Output and the Pattern (as much as you know). CONTENTS: Section : I need a Regex Pattern ASAP! - My first Regex Post Section : Regex samples that you can use! Section : I need help applying regex in UiPath Section : Regex Matches and Groups Section : Le…

[CheatSheet] - System.Text.RegularExpressions | RegEx

This CheatSheet introduces the basic use of regex functions. With further examples also special cases are presented. Introduction From the namespace System.Text.RegularExpressions following methods are offered: Regex.Match Regex.Matches Regex.isMatch Regex.Replace Regex.Split A simple usage for example would look like this: [grafik] Recommendation: add System.Text.RegularExpressions to the imports: [grafik] it allows to use the shortened statement, as the namespace part can be ommited…

Also, hands on with the Regex Expression editors/testers would do a good practice :

regex101: build, test, and debug regex

[1a2bc346dgjk7544ebjkkbvf (Mi uipath s)]

I will check ,Thanks !