Finding a word in a sentence

[Sindhu]

Hii friends,

Good Afternoon...

I have some set of words in excel which i need to search in a sentence. If i find any words of the set in the sentence, then it should be returned.

Eg:
Sentence:
I am poor developer
I am great developer
you may be best developer

Lookup(Set of key words):
great
poor
best

Expected o/p:

I am poor developer poor
I am great developer great
you may be best developer best

Can anyone help me out to find a solution for this problem.?




Thanks in Advance
Sindhu

 

[dscg]

With sentences in A2:A4, and keywords in A7:A9, try this macro. Adjust ranges as needed.

Option Explicit
Sub Sindhu()
Dim i As Integer
Dim r As Range
Dim v

v = Application.Transpose(Range("A7:A9"))

For i = LBound(v) To UBound(v)
    With ActiveSheet.Range("A2:A4")

        On Error Resume Next
        Set r = .Find(What:=v(i), After:=.Cells(.Cells.Count), LookIn:=xlFormulas, LookAt:=xlPart, _
                      SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        On Error GoTo 0

        If Not r Is Nothing Then
            r = r.Value & " " & v(i)
        End If

        Set r = Nothing

    End With
Next i

End Sub

HTH!