Regular Expression in excel VBA

Question

I'm trying to use regex in excel VBA to match a pattern within all cells in a column range, and remove the matched patterns to a new column range.

E.g.

Happy Day Care Club (1124734)
French Pattiserie (8985D)
The King's Pantry (G6666642742D)
Big Shoe (China) Ltd (ZZ454)

Essentially I want to remove the last bracketed portion of each string and transpose this part (without the brackets) into a different column range.

The regex I have so far is "(([^)]+))\z" (which I don't know if this is actually correct), and embedded within this VBA:

Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim Myrange As Range

Sheets("Sheet 1").Activate
Range("FF65536").End(xlUp).Select
LastCell = ActiveCell.Address

Set Myrange = ActiveSheet.Range("FF2:" & LastCell)

For Each C In Myrange
    strPattern = "(\(([^\)]+)\)\z)"

    If strPattern <> "" Then
        strInput = C.Value
        strReplace = "$1"

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.Test(strInput) Then
            Range("FF2").Select = regEx.Replace(strInput, "$1")
            Range("DX2").Select = regEx.Replace(strInput, "$2")
        End If
    End If
Next

I'm a newbie so please forgive glaringly obvious mistakes.

Many thanks,

If the pattern is the same, as in the bracketed portion is always at the end you could use the split function. It would use a lot less code to get the same thing. — Scott Craner
– Scott Craner, Commented Oct 23, 2015 at 14:02
You can also use this \s*($(.*?)$)$ regex if there are ( or ) inside the parentheses. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Oct 23, 2015 at 14:06

Code Different · Accepted Answer · 2015-10-23 14:12:33Z

2

No your regex pattern isn't correct. You should test your pattern separately as regex is its own mini-language. Try this pattern (Regex101):

\((.+)\)$

About the gm options: g means Global, m means Multiline, both of which are set to True in your code.

answered Oct 23, 2015 at 14:12

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

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

Comments

Rory · Accepted Answer · 2015-10-23 14:13:06Z

0

Here's a non-RegEx method:

Dim Myrange As Range

Sheets("Sheet 1").Activate

Set Myrange = ActiveSheet.Range("FF2:FF" & Cells(Rows.Count, "FF").End(xlUp).Row)
With Myrange
    .Offset(, -43).Value = .Worksheet.Evaluate("INDEX(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(" & .Address & _
                                            ",""("",REPT("" "",500)),500)),"")"",""""),)")
End With

answered Oct 23, 2015 at 14:13

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Comments

rheitzman · Accepted Answer · 2015-10-23 19:56:46Z

0

Personally I would resort to RegEx as a last resort...

Here is a snippet using string functions:

Dim iRow As Long
Dim s As String
For iRow = 1 To UsedRange.Rows.Count
    Debug.Print Cells(iRow, 1).Value
    s = Cells(iRow, 1).Value
    s = Trim(Left(s, InStrRev(s, "(") - 1))
    Debug.Print s
Next

The relevant line being Trim(Left(s, InStrRev(s, "(") - 1)). You would need QA check to deal with data w/o proper format.

answered Oct 23, 2015 at 19:56

rheitzman

2,3073 gold badges20 silver badges36 bronze badges

Collectives™ on Stack Overflow

Regular Expression in excel VBA

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related