Here is a VBA solution.
Option Explicit
Option Compare Text
Sub FindText()
Dim crit As String, s As String, x As String
Dim i As Long, j As Long, lr As Long, lr2 As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
lr2 = Range("C" & Rows.Count).End(xlUp).Row
For j = 2 To lr2
s = ""
x = ""
crit = Range("C" & j)
For i = 2 To lr
If InStr(Range("A" & i), crit) > 0 Then
Range("D" & j) = "Found"
x = Range("A" & i).Address
s = s & " ," & x
Range("F" & j) = s
End If
Next i
Next j
End Sub
How to install your new code
- Copy the Excel VBA code
- Select the workbook in which you want to store the Excel VBA code
- Press Alt+F11 to open the Visual Basic Editor
- Choose Insert > Module
- Edit > Paste the macro into the module that appeared
- Close the VBEditor
- Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
To run the Excel VBA code:- Press Alt-F8 to open the macro list
- Select a macro in the list
- Click the Run button
Bookmarks