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
