VBA code to find the string and paste the whole sentence in another cell

[kissisvarmas]

hi, I want a VBA code in excel that identifies string in a particular cell and copy the whole sentence and paste it in another singe cell.

for ex:

strings:"forecast, estimate, guidance"

Line in cell("A1") contains the text
"ABC company is providing results for the year of 2016. the company also providing forecasts for the year of 2017. the company announces an upward revision to the company's previously announced 2016 production . For the period, the company expects 2016(e) exit production of 13,500 boe/d (79% liquids). Expected total capital spending is USD 66 million. Estimated funds flow from operations is USD 74 million."

i want to extract the sentences which contains the above strings and result should be

Line in cell("B1") contains the text
the company also providing forecasts for the year of 2017.For the period, the company expects 2016(e) exit production of 5000 boe/d (79% liquids). Expected total capital spending is USD 100 million. Estimated funds flow from operations is USD 74 million.

So, can any one provide the macro for the above criteria

[mana]

Sub test()
Dim v
Dim doc As String
Dim s
Dim i As Long, j As Long
Dim tmp As String

v = Array("forecast", "estimate", "guidance")

doc = Range("a1").Value
doc = Replace(doc, ". ", ". " & vbTab)
s = Split(doc, vbTab)

For i = LBound(s) To UBound(s)
For j = LBound(v) To UBound(v)
If InStr(LCase(s(i)), v(j)) > 0 Then
tmp = tmp & " " & s(i)
Exit For
End If
Next
Next

Range("b1").Value = Trim(tmp)

End Sub

[kissisvarmas]

ya...code working ...as many thanks to you...