Matching and Displaying a RegEx Pattern in a String and storing in Excel Rows

Suppose in Column A we have certain text and we want to search a pattern/certain words in the row and store the searched/found words, separated by comma in very next column B's respective row.

For that we can use the below Excel VBA code. Put the text data in Column A and select them and then run the below code, the results would be stored in column B. And Column C will have the label as per the words in Column 2. Change the Regex pattern as per your need.

Please note In order to use Regex in VBA, you first have to set the reference in the VBE editor. In the VBE editor, go to Tools>References>Microsoft VBScript Regular Expressions.

Below is the VBA code for use.

Sub amiq()

Dim strPattern As String: strPattern = "oil|gas|coal|sport|physical|gym|fitness"

Dim regEx As New RegExp

Dim strInput As String

Dim r As Integer

For r = 2 To Selection.Rows.Count

If strPattern <> "" Then

strInput = Cells(r, 1).Value

Values = ""

With regEx

.Global = True

'.MultiLine = True

.IgnoreCase = True

.Pattern = strPattern

End With

Set theMatches = regEx.Execute(strInput)

For Each Match In theMatches

Values = Values & ", " & Match.Value

If Values <> "" Then

Cells(r, 2).Value = Values

Else

Cells(r, 2).Value = "Not FOUND"

End If

If InStr(1, Cells(r, 2).Value, "oil", 1) > 0 or InStr(1, Cells(r, 2).Value, "coal", 1) > 0 or InStr(1, Cells(r, 2).Value, "gas", 1) > 0 then

Cells(r, 3).Value = "Energy

elseif InStr(1, Cells(r, 2).Value, "sport", 1) > 0 or InStr(1, Cells(r, 2).Value, "physical", 1) > 0 then

Cells(r, 3).Value = "Sports"

elseif InStr(1, Cells(r, 2).Value, "Gym", 1) > 0 or InStr(1, Cells(r, 2).Value, "fitness", 1) > 0 then

Cells(r, 3).Value = "Gym"

end if

Next

End If

Next r

End Sub