Solved: Find specific word from text string and poulate in seperate cell (continuation)

[JKwan]

http://www.vbaexpress.com/forum/showthread.php?t=43601

with the above link, it only picks up the first instance of the occurance of the word. I am wondering if the formula can pick up all / mutiple instances of the words inside the array.

if the sentence is:
- blue blue - pick up both (not just one blue, the first occurance)
- blue black - pick up both

[Teeroy]

Not sure if a Formula could do it but here's an example of a VBA way to pick up multiple matches in a string using a Regular Expression.

[vba]
Sub Multiple_Match_Find()
Dim re As Object
Dim words As String
Dim m

Set re = CreateObject("vbscript.regexp")

words = "Testing Blue is Blue"
With re
.Global = True
.IgnoreCase = True
.Pattern = "Blue"
For Each m In .Execute(words)
MsgBox m.FirstIndex
Next
End With
End Sub[/vba]

[mikerickson]

You might try this UDF

[VBA]Function DeleteExcept(ByVal aString As String, ExcludeFromDeletion As Variant) As String
Dim WordsInString As Variant, i As Long

WordsInString = Split(aString, " ")
For i = LBound(WordsInString) To UBound(WordsInString)
If IsError(Application.Match(WordsInString(i), ExcludeFromDeletion, 0)) Then
WordsInString(i) = vbNullString
End If
Next i
DeleteExcept = Application.Trim(Join(WordsInString, " "))
End Function
[/VBA]
If "The blue cat sat on the black and red mat" is in A1 and "black";"blue";"red";"green" is in H1:H4 then

=DeleteExcept(A1,H1:H4) will return "blue black red"

[snb]

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"black";"");"blue";"");"red";"")) 

[JKwan]

Thank you all for the help. mikerickson's solution works the best

[snb]

=TRIM(IF(LEN(SUBSTITUTE(A1;"black";""))<>LEN(A1);"black";"")&IF(LEN(SUBSTITUTE(A1;"blue";""))<>LEN(A1);" blue";"")&IF(LEN(SUBSTITUTE(A1;"red";""))<>LEN(A1);" red";"")&IF(LEN(SUBSTITUTE(A1;"green";""))<>LEN(A1);" green";"")) 

or

=TRIM(IF(LEN(SUBSTITUTE(A1;H1;""))<>LEN(A1);H1;"")&IF(LEN(SUBSTITUTE(A1;H2;""))<>LEN(A1);" " & H2;"")&IF(LEN(SUBSTITUTE(A1;H3;""))<>LEN(A1);" " & H3;"")&IF(LEN(SUBSTITUTE(A1;H4;""))<>LEN(A1);" " & H4;""))