Excel-vba regex pattern

Question

I need the following function to check a string if it looks like this:

• Any digit (including fraction and decimal)
• optional one of the synonyms of pieces / bags / boxes
• optional one of the male / female synonyms

there can be a space between each, and the order of 2 optional parts should not matter ("2 box male" == "2 male box")

but instead of doing something useful, this function excludes everything that starts with a digit:

Function validAmount(Zelle As Variant) Set regEx = CreateObject("VBScript.RegExp") regEx.IgnoreCase = True regEx.Pattern = "\d\s?(pcs|pieces|piece|pc|stk|bags|bag|box|bx|boxes)?\s?(male|m|female|f)?" If (regEx.test(Zelle)) Then validAmount = True Else validAmount = False End If End Function 

I hope my mistake is not too stupid.

edit I thought of an extra feature. how can I allow several objects of the template above, separated by the symbol ",", like "1 boxing woman, 3 bags of m, 4pcs of male"

ps: both solutions work very well, but allow something like this "1 male female" or "2 boxed bag"

edit2: first of all: I am very grateful for your help! I would never decide it myself! and I wanted to click "allow" (I can not press "up" because my reputation is still too low), as soon as everything works as planned. If I had to click on it earlier, I'm sorry. His first question is here, and I'm a little overwhelmed by how quickly your answers arrive :)

I think I can not express my wishes: D is very sorry! here is the third attempt: only (at least) one of each group should be allowed. the true source data should be: "# box gender" "# gender box" "# box" "# Gender" "#" but not: "# box gender" or "# gender gender"

@sln: I think your first look is more like me, but it allows you to use two instances of the same group, even if it is looking for me, since it should not :( (the same for JMax solution)

@JMax: I like your "split" solution! havent even thought about this simple trick: D I was so fixed in regular expression that I was thinking about something else

Answer 1

Here is an attempt:

 Function validAmount(Zelle As String) Dim sBoxes As String, sGender As String Dim arr() As String Dim i As Integer arr = Split(Zelle, ",") sBoxes = "pcs|pieces|piece|pc|stk|bags|bag|box|bx|boxes" sGender = "male|m|female|f" validAmount = True For i = 0 To UBound(arr) Set regEx = CreateObject("VBScript.RegExp") regEx.IgnoreCase = True regEx.Pattern = "\d\s?(((" & sBoxes & ")?\s?(" & sGender & "))|((" & sGender & ")?\s?(" & sBoxes & ")))?$" If (regEx.test(arr(i))) Then validAmount = validAmount And True Else validAmount = validAmount And False End If Next i End Function 

And here is the testing procedure:

 Sub unitTest() 'could use debug.Assert too Debug.Print (validAmount("1 box") & " should be True") Debug.Print (validAmount("1 boxe male") & " should be False") Debug.Print (validAmount("1 pcs female") & " should be True") Debug.Print (validAmount("1boxes") & " should be True") Debug.Print (validAmount("1 female pcs") & " should be True") Debug.Print (validAmount("boxes") & " should be False") Debug.Print (validAmount("2 male box") & " should be True") Debug.Print (validAmount("1 mytest") & " should be False") Debug.Print (validAmount("1 pc box") & " should be False") Debug.Print (validAmount("1 box box") & " should be False") Debug.Print (validAmount("1 box female, 3 bags m, 4pcs male") & " should be True") End Sub 

I changed the fields and gender in the lines so you can add both to your template (I don’t know how to check the order without this trick. Does anyone have a better idea? I also added $ to tell Excel that this was the end of the line otherwise, any string starting with a digit is transmitted.

Answer 2

This will work if your vba can at least make forward-looking statements. I thought I was reading that this is a regex like javascript. If so, it works. But neglect if not.

It is not so easy to explain, therefore there is a destruction of its functionality.

^\d(?:\s?(?:(?!\1)(?:pcs|pc|pieces|piece|stk|bags|bag|boxes|box|bx)()|(?!\2)(?:male|m|female|f)()|)){2}$

Expand:

 ^ # Begining of string \d # A digit (?: # Grouping \s? # Optional whitespace (?: # Grouping (?!\1) # Never been here before, capt grp 1 is undefined (?:pcs|pc|pieces|piece|stk|bags|bag|boxes|box|bx) # One of these alternations is found () # Mark we've been here, Capt Grp 1 is defined | # OR .. (?!\2) # Never been here before, capt grp 2 is undefined (?:male|m|female|f) # One of these alternations is found () # Mark we've been here, Capt Grp 2 is defined | # OR .. # Nothing, this allows to pick optional \s only, or combined ) # End grouping ){2} # End grouping, do exactly 2 times $ # End of string 

Edit

To solve your changes in one shot, the following regular expressions will work. If you want to make any group of alternatives acceptable, combine them into one rotation. There may be problems with this if name conflicts arise. This is because you do it twice. Also, is there a big problem interval?

Below are 2 regular expressions.

This is a very strict interval. This is more consistent with how you state the problem. This is not a good idea.

^(?=\d)(?:\d(?:\s?(?:pcs|pc|pieces|piece|stk|bags|bag|boxes|box|bx|male|m|female|f|)){2}(?:,\s?(?=\d)|$))+$

It totally relaxes from a distance, maintaining an opaque corect shape.
This is the recommended version. Feel free to re-design the spacing in the regular expression.

^(?=\s*\d)(?:\s*\d(?:\s*(?:pcs|pc|pieces|piece|stk|bags|bag|boxes|box|bx|male|m|female|f|)){2}\s*(?:,(?=\s*\d)|$))+$

Here it is expanded, see comments from the original extended regular expression above for a difference.

 ^ (?=\s*\d) (?: \s* \d (?: \s* (?:pcs|pc|pieces|piece|stk|bags|bag|boxes|box|bx|male|m|female|f|) ){2} \s* (?: , (?=\s*\d) | $ ) )+ $