Regex to check if a string separated by pipeline contains a certain keyword

Help Studio
, , , ,
1

Hi,

I have a list of fruits that I loop each of it to check if it is found in a string. I would like to use regex to check if current keyword also has a prefix of “exclude” or “excluding” so I can bypass/skip it.

Example:

Possible formats:
input_string = Apple | Mango | All Fruits (exclude Orange) | Melon | Guava/Grapes | Excluding: Banana, Strawberry, Cherry | Avocado

input_string = Apple | Mango | All Fruits (exclude Orange) | Melon | Guava/Grapes | Exclude: Banana, Strawberry, Cherry | Avocado

input_string = Apple | Mango | All Fruits (exclude Orange) | Melon | Guava/Grapes | Excludes Banana, Strawberry, Cherry | Avocado

List of keywords = Result:
Apple = Proceed
Orange = Skip
Melon = Proceed
Banana = Skip
Strawberry = Skip
Cherry = Skip
Mango = Proceed
Avocado = Proceed

P.S. There could be a character/s in-between “exclude” and the keyword.

Thanks in advance.

2

Hi @mnlatam ,

Could you let us know if there would be any other variations of the data format ?

Currently we see two patterns with exclude and excluding also brackets are being used for exclude. Do we have any other such patterns ? Is it possible that it is irregular at times ?

3

Hi @supermanPunch,

I have edited the post for the possible data formats. Thanks!

4

@mnlatam ,

Could you check with the below Expression :

Regex.Replace(yourInputString,"(?=\(?(excludes|excluding|exclude):?).*?(?>=\)|\|)","|",RegexOptions.IgnoreCase)

Visuals :
image

We get the updated String where the excluding items are removed and is not present in the updated string, You should then be able to perform the comparison by checking if the item is present in the string.

5

Thanks @supermanPunch it is working in Studio but original code is using Invoke VBA to call a macro to check the keyword, looks like Positive Lookahead is not working in VBA macro.

6

@mnlatam ,

Do you require it in VBA only ? Can we not transfer the Output to the VBA (True/False) if there are further processing with VBA to be done ?

Also, Could you let us know what was the error message received ?

7

RegEx isn’t the best way to do this.

Split(input_string," | ").Contains(“exclude”)

8

Hi @supermanPunch, in my VBA code I’m iterating through a list of fruits, first loop is Orange but since it has a prefix exclude I need to skip it, I need to know which fruits I am skipping.

9

@mnlatam ,

Is it possible for you to provide the VBA Code as well ? Wanted to check if we can make it easier, also want to know what are the values that are being passed to the VBA.

10

sample vba.txt (872 Bytes)

@supermanPunch , here’s the sample VBA code. I simplified it for better understanding. Basically I need to incorporate the fruit to the regex that I will be using.