excel-vba regex pattern

Question

I want the following function to validate a string if it looks like this:

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

between each may be a space and the order of the 2 optional parts shouldnt matter ("2 box male" == "2 male box")

but instead of doing anything useful this function excepts 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 error isn't too stupid

edit: I thought of an additional feature. how could I allow multiple entities of the pattern above seperated by a "," like "1 box female, 3 bags m, 4pcs male"

ps: both solutions work pretty well but allow something like this "1 male female" or "2 box bag"

edit2: first of all: I am really thankful for your help! i would have never solved this on my own! and I wanted to click "solved" (cant click "up" cause my reputation is still too low) as soon as everything works as intended. If I should've clicked on it earlier I'm sorry. Its my first question here and I'm a little overwhelmed of how fast your answers come :)

I think I'm unable to express my wishes :D really sorry for that! here is a third try: only (at least) one of each groups should be allowed. genuine inputs should be: "# box gender" "# gender box" "# box" "# gender" "#" but not: "# box box" or "# gender gender"

@sln: I think your first looks more like I want it but it allows two instances of the same group even though it looks for me as it shouldnt :( (same for JMax solution)

@JMax: I love your "split" solution! havent even thought of this simple trick :D I was so fixed on regex that a havent thought of anything else

you should have the privilege to upvote now :)

JMax
– JMax

2011-12-08 16:17:42 +00:00
Commented Dec 8, 2011 at 16:17 — JMax
– JMax, Commented Dec 8, 2011 at 16:17

JMax · Accepted Answer · 2011-12-08 16:17:03Z

3

Here is a try:

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 a 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've changed boxes and gender into strings so that you can add both in your pattern (I don't know any way to check the order without that trick. Does anybody have a better idea?
I also added a $ to tell Excel this was the end of the string, else any string begining with a digit would pass.

edited Dec 8, 2011 at 16:17

answered Dec 6, 2011 at 19:48

JMax

26.7k12 gold badges74 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Marc Over a year ago

works almost perfectly. the only problem is that strings with two of the same group ("1 pc box" or 1 "female male") are also exepted

JMax Over a year ago

My solution worked for this case too... I've edited my post for your new requirements (array as an input)

JMax Over a year ago

I've edited my solution that will check either the box first of the gender first. My unit tests pass. If it's still doesn't work, you can try to show us what should work and what shouldn't as my unit tests show

Marc Over a year ago

works like a charm :) and it's quite simple! one would think I should have come up with this idea myself. nevertheless: big thanks :)

score 2 · Accepted Answer · 2011-12-07 19:34:46Z

2

This will work if your vba can at least do lookahead assertions. I thought I read it does regex like javascript. If so, this works. But disregard if not.

Its not that easy to explain, so there is a breakdown of its functionality.

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

Expanded:

^             # 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 address your edits in one shot, the below regexes will work. If you wanted to make either group of alternations acceptable, combine them into one alternation. There could be problems doing it this way if naming conflicts occur. This is because you are doing it twice. Also, is the spacing a big issue any more?

There are 2 regex below.

This one is very strict on spacing. Its more in line with the way you state the problem. Its not really a good idea.

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

This one is totaly relaxed on spacing while maintaning the corect non-whitespace form.
This is the recommended version. Feel free to change the spacing constructs in the regex.

^(?=\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 the comments from the original expanded regex above for the difference.

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

edited Dec 7, 2011 at 19:34

answered Dec 6, 2011 at 22:18

user557597

6 Comments

Gaijinhunter Over a year ago

Look ahead is supported. Look behind, no. :)

user557597 Over a year ago

Ok, then this regex will work. There is no reason for look behind anyway.

Marc Over a year ago

works almost perfectly. the only problem is that strings with two of the same group ("1 pc box" or 1 "female male") are also exepted

user557597 Over a year ago

@Marc - You didn't say that in the original problem description. That simplifies it to one big alternation. See my edit. The original regex I posted is fairly sophisticated, designed to handle positioning. If any of this works the way you intended, you should up-vote and mark answered, otherwise there is no incentive to help people.

JMax Over a year ago

Thanks for your regex and the detailed explanation (+1btw). @Issun: what is look ahead VS look behind (just point me to any good web ressource if you have any not too far)? For the last requirement of the OP, I've used an array and a split, see my answer and comment it if you have any suggestion :)

|

Collectives™ on Stack Overflow

excel-vba regex pattern

2 Answers 2

4 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related