Find some words in a string

A small UDF does the trick:

Function FindWords(stInput As String)
Dim stOutput As String, x
For Each x In Array("butter", "honey") 'list words here
    If InStr(stInput, x) > 0 Then
        stOutput = stOutput & " " & x
    End If
Next x
FindWords = Trim(stOutput)
End Function

Use as:

Formula:

=findwords(A1)

Originally Posted by larrygoldstein

Thank you very much that's great ! Now, instead of writing the list of words in the VBA code, .....
How can I do it ? Thanks

Hello Larry,

Just an observation which I hope you'll accept in the way it's intended. When posing a question try to make sure all facts or requirements are known in the first post. This avoids people needing to revisit the task. On this occasion I guess it wasn't too onerous but I've seen threads when the final requirement for a real world workbook is far removed from an example and simplified original workbook.

try:

Function FindWords(stInput As String)
Dim stOutput As String, stList() As String
Dim rng As Range, x

Set rng = Sheet1.Range("A1") 'Amend as necessary
stList = Split(Replace(Replace(rng.Value, """", ""), " ", ""), ",")

For Each x In stList
    If InStr(UCase(stInput), UCase(x)) > 0 Then
        stOutput = stOutput & " " & x
    End If
Next x
FindWords = Trim(stOutput)
End Function

Use a cell in this workbook to link to the list of words in another workbook, if necessary, and reference the local range in the function (commented line).