PDA

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



JKwan
09-12-2012, 07:30 AM
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
09-15-2012, 06:17 PM
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.


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

mikerickson
09-15-2012, 07:10 PM
You might try this UDF

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

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
09-16-2012, 06:19 AM
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"black";"");"blue";"");"red";""))

JKwan
09-16-2012, 07:10 AM
Thank you all for the help. mikerickson's solution works the best

snb
09-16-2012, 07:53 AM
=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;""))