Write several regex matches to different cells in excel

Question

I'm writing a excel macro to be able to search an excel list and, among other things, write the matches (if any) to different cells.

I got a lot of help from this great explanation but what I can't figure out is how to only write the regex match to the cell. My current code cuts the string after the match and writes this to the cell. But I would like to only write the match, nothing else from the string.

This is my code:

Private Sub simpleRegex()
Dim strPattern As String
Dim strReplace As String
Dim regEx As New RegExp
Dim strInput As String
Dim Myrange As Range

Set Myrange = ActiveSheet.Range("A1:A5")

For Each cell In Myrange
    strPattern = "(storlek|strl|stl|strlk|storleken|storl|size|storleksmärkt|storl|storlk|st)(.{0,2}?)((30|32|34|36|38|40|42|44|46|48|50))(.?)((30|32|34|36|38|40|42|44|46|48|50)?)"

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

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

        If regEx.test(strInput) Then
            cell(1, 5).Value = 1
            cell(1, 2).Value = regEx.Replace(strInput, "$1")
            cell(1, 3).Value = regEx.Replace(strInput, "$2")
            cell(1, 4).Value = regEx.Replace(strInput, "$3")

        Else
            cell(1, 6).Value = 1
        End If
    End If
Next
End Sub

This is the result I get in excel:

enter image description here

So the red text in column A is the full match from the initial string and the red in column B and D is the matches separated. So it's almost as I want it, but I would like to only have the match in the cell B-D not the whole string.

Sorry for the swedish in the example, my dataset is from a swedish site. But I think you get the problem anyway?

Then why do you `Replace`? `Execute` and grab the match value. — Wiktor Stribiżew, Mar 02 '17 at 14:57
Aha, you need the submatches 1 through 3 in B-D columns. Run `regEx.Execute` and grab these values. — Wiktor Stribiżew, Mar 02 '17 at 15:12
Thanks guys that helped. I'm just learning vb and regex so I hadn't understood how to use the execute method. But now I got it, at least almost! Thanks for your help :D — Cecilia Mattsson, Mar 02 '17 at 15:37

score 0 · Accepted Answer · answered Mar 02 '17 at 16:27

You need to use .regEx.Execute(str) and access the SubMatches values:

Dim objMatchs As MatchCollection
' ...
Set objMatches = regEx.Execute(strInput)
If objMatches.Count <> 0 Then
  cell(1, 5).Value = 1
  cell(1, 2).Value = objMatches(0).SubMatches(0)
  cell(1, 3).Value = objMatches(0).SubMatches(1)
  cell(1, 4).Value = objMatches(0).SubMatches(2)
End If

The capture group IDs start with the 0 based index.

The objMatches(0).SubMatches(0) means get the first match, the first capturing group value.

Write several regex matches to different cells in excel

1 Answers1