Keep text matching looping search with wild cards

[Fresh]

Hello!

I am trying to write a function that will keep the text matching a search with wild cards. The text in a given cell is structured as follows:

Cell A1:
0001: greenlight go, 0002: red car, 0003: monsterblue
Cell A2:
0006: pink thing, 0076: applered

I now want to search for the word red and the word blue, either stand-alone, or part of another word. This results in the following new structure:

Cell B1:
0002: red car, 0003: monsterblue
Cell B2:
0076: applered

Below is the code that I have so far. I struggle to get it to loop between the colons and commas throughout the text inn the cell. Any suggestions?

____________________________

Function findNum(rng)
Dim text As String, text2 As String, s1 As String, s2 As String, s3 As String
Dim number1 As Integer, number2 As Integer, number3 As Integer

On Error Resume Next

'Words that should match textstring
s1 = "*blue*"
s2 = "*red*"
s3 = "*green*"

'If no words match function shows empty
text2 = ""

'Here I'd like the function too start looping. The function searches for the words between colon and comma
text = rng & ","
text = Mid(text, InStr(1, text, ":") - 4, InStr(1, text, ",") - InStr(1, text, ":") + 4)

'Testing for any of the three words
If IsError(WorksheetFunction.search(s1, LCase(text), 1)) = True Then
number1 = 1
End If
If IsError(WorksheetFunction.search(s2, LCase(text), 1)) = True Then
number2 = 1
End If
If IsError(WorksheetFunction.search(s3, LCase(text), 1)) = True Then
number3 = 1
End If
If number1 + number2 + number3 < 3 Then
text2 = text
End If

'If any word found: keep text: loop --> If any word found: keep text: loop

findNum = text2

End Function

 

[Fresh]

Hello again!

Managed to find the solution myself using arrays. Posting the code below:

____________________

Function CleanUp(Text As String)

Dim arr_matrise As Variant, arr_s1 As Variant, arr_s2 As Variant, arr_s3 As Variant
Dim CleanText As String

On Error GoTo ErrHandler

' Split text at commas
arr_matrise = Split(Text, ",")

' Filter arrays with search word
arr_s1 = Filter(arr_matrise, "blue", True, vbTextCompare)
arr_s2 = Filter(arr_matrise, "red", True, vbTextCompare)
arr_s3 = Filter(arr_matrise, "green", True, vbTextCompare)

' Join matching text together
CleanText = Join(arr_s1, ",") & "," & Join(arr_s2, ",") & "," & Join(arr_s3, ",")

' Clean up the text a bit

' Remove double commas
CleanText = WorksheetFunction.Substitute(CleanText, ",,", ",")

' Remove comma in front of text, if any
If InStr(1, CleanText, ",") = 1 Then
CleanText = Right(CleanText, Len(CleanText) - 1)
End If

' Remove unneccecary spaces
CleanText = Trim(CleanText)


CleanUp = CleanText

Exit Function

ErrHandler:
CleanUp = ""

End Function