Regex within Regex Groups in VBA

Question

I am coming from this question: How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

Found this very useful link there: https://www.experts-exchange.com/articles/1336/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

That is what I will use for the functions as I need to do this in VBA. However, the Regex that I am trying to solve is something like below:

OR(Q12 = "YES", Q13 = "ABCD", Q2 <> 3)

It will always only be OR function

I have 2 columns in my excel worksheet which are like:

Table1
    ColA   ColB
    Q1     YES
    Q2     2
    Q12    YES
    Q13    ABCD

So am trying to first make n groups:

Group1: Q12 = "YES"
Group2: Q13 = "ABCD"
Group3: Q2 <> 3

Then, I have to tackle every such group by again doing a Regex which will give me:

Group1-1: Q12
Group1-2: =
Group1-3: YES

And then I will VLOOKUP(Q12, Table1, 2, FALSE) within the VBA code. If Group1-2 is "=", then =, else <> Group1-3

This will mean Group1 = 1 Similarly for all n Groups

TLDR:

Input:

OR(Q12 = "YES", Q13 = "ABCD", Q2 <> 3)

Output:

Group1-1: Q12
Group1-2: =
Group1-3: "YES"

Group2-1: Q13
Group2-2: =
Group2-3: "ABCD"

Group3-1: Q2
Group3-2: <>
Group3-3: 3

Thank you very much!

Edit1: Hey, instead of Hye

score 2 · Accepted Answer · answered Nov 22 '19 at 08:35

2

No need for using regular expressions at all :) Just remove commas and split by space:

Sub NoRegex()
    Dim str As String, splitted() As String
    str = "OR(Q12 = ""YES"", Q13 = ""ABCD"", Q2 <> 3)"
    str = Replace(Replace(Replace(Replace(str, ",", ""), "OR", ""), ")", ""), "(", "")

    splitted = Split(str, " ")
End Sub

You will have all your chunks in an array :)

answered Nov 22 '19 at 08:35

Michał Turczyn

32,028
14
47
69

Haha.. I guess it is this simple afterall.. Thank you so much! I just had one more qs on this - I do not know how many 'replaces' Ill have to put. Can range from 0 to 5 [0 as in some cases there wont be an OR function, it will just be a simple Q9 = "XYZ" – spiff Nov 22 '19 at 09:18
@spiff Just try it with Q9 = "XYZ" yourself - it'll work :) – Michał Turczyn Nov 22 '19 at 09:35

Regex within Regex Groups in VBA

1 Answers1