Find Words From One Range In Another

[syl]

I need a way to do the following in Excel:

column A has text
column B needs a Formula that returns specific value(s)
ie. want to find any of 17 specific strings within a row in column A and return that (those) string value(s) in column B

for example row1 column A contains the following:
Troubleshooter Notes: Sound KVSND Tree was not used as doc exists that covered the specific issue

The formula in column B needs to be able to find any of the following values (note: these values could be placed in a lookup table):
"KVBAT" "KVBLTH" "KVDNW" "KVDOC" "KVDWB" "KVFLSH" "KVKBD" "KVLAN" "KVODD" "KVOTH" "KVPFS" "KVRCVY" "KVSB2" "KVSFT" "KVSND" "KVVID" "KVWFI"

and place the found value(s) in row 1 column B

in the above example column B would return the value:
KVSND

the formula needs to be diverse enough to return multiple values if more than 1 value is found

for example row1 column A:
Troubleshooter Notes: Sound KVSND Tree was not used as doc exists that covered the specific issue, Performance Security Troubleshooter KVPFS The system has worked previously performance expectation is being set by previous performance or a message

row 1 column B should return the following values (preferably with a space between each value):
KVSND KVPFS

Thank you for your help,
syl

[Dave Hawley]

Create 2 Dynamic Named Range. Name the one with "KVBAT" "KVBLTH" "KVDNW" etc as LookRange and the other FindRange. Now run the code below. It will place the matching into the Column (same row as match) to the immediate right of FindRange.

Sub FindText()
Dim rCell As Range, rFindIn As Range
Dim strWord As String, lLoop As Long
Dim rFound As Range

    Set rFindIn = Range("FindRange")
    
    For Each rCell In Range("LookRange")
    
        strWord = rCell
        Set rFound = rFindIn.Cells(1, 1)
        
        For lLoop = 1 To WorksheetFunction.CountIf(rFindIn, "*" & strWord & "*")
        
           Set rFound = rFindIn.Find(What:=strWord, After:=rFound, LookIn:=xlValues, LookAt _
                :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False, SearchFormat:=False)
                
                rFound(1, 2) = strWord
                
        Next lLoop
    Next rCell

End Sub

[syl]

Thank you Dave Hawley for your quick response. You ROCK!
I will definately try this tomorrow when I get back to work. This will save me a lot of time (as I spent all day trying to figure this out with basic excel formulas and got nowhere).
I am wondering if it would be a big or small modification to have the returned value in a non-adjacent cell for example instead of returning the value in row 1 column B, having the value returned in row 1 column Z? If its a big deal to make that modification then I'll just stick with the great help you have given me.

[Dave Hawley]

The line of code rFound(1, 2) = strWord adds the word next to the row it was found in. The 1 means same row, and the 2 means one column to the right. In other words, to have it placed in the same row 5 columns to the right, it would be rFound(1, 6) = strWord. To have it offset one row down 5 columns to the right it would be rFound(2, 6) = strWord.