Finding all instances of a certain string

Question

I was wondering if there was a way to filter a range full of "random text".

Columns B (cell B4) holds all of this random text. This looks something like this:

ColumnB
------
Apple2
Apple45
Apple62
Strawberry23
Orange
Apple312
Apple978
Apple725
Apple6
Peanut32
Orange23
Pear32

I was wondering if I could filtered all the text that has apple in it to a separate column and have it look like something like this in cell C4:

ColumnC
-------
Apple2
Apple45
Apple62
Apple312
Apple978
Apple725
Apple6 

Please note that all of this text is all in one cell and not broken up in different rows.

EDIT: This is what I have so far:

Sub WildcardSearch()

For Each Rname In Range("B4")
    If Rname.Value Like "*Apple*" Then
        Split("B4", vbLf)
    End If
    
Next Rname

End Sub

I was wondering if there was a way to build an array with new strings added to the array after it finds another "apple"? Not sure if im thinking along the right direction though.

Answer 1

Get Criteria Substrings

The Function

Function GetCriteriaSubStrings( _
    ByVal InitialString As String, _
    ByVal CriteriaString As String, _
    Optional ByVal sDelimiter As String = ",", _
    Optional ByVal dDelimiter As String, _
    Optional ByVal CompareMethod As VbCompareMethod = vbTextCompare) _
As String
    Dim Dat() As String: Dat = Split(InitialString, sDelimiter)
    Dim i As Long, r As Long
    For i = 0 To UBound(Dat)
        If InStr(1, Dat(i), CriteriaString, CompareMethod) > 0 Then
            Dat(r) = Dat(i)
            r = r + 1
        End If
    Next i
    If r > 0 Then
        If dDelimiter = "" Then
            dDelimiter = sDelimiter
        End If
        ReDim Preserve Dat(0 To r - 1)
        GetCriteriaSubStrings = Join(Dat, dDelimiter)
    End If
End Function

VBA

Sub WriteCriteriaSubStrings()
    Range("C4").Value = GetCriteriaSubStrings(Range("B4").Value, "Apple", vbLf)
End Sub

Excel

=GetCriteriaSubStrings(B4,"Apple",CHAR(10))

Answer 2

Try this code:

Sub test1()
    Range("C4").Value = Join(Filter(Split(Range("B4"), vbLf), "apple", True, vbTextCompare), vbLf)
End Sub