Transform User Defined Function to Sub (Filter Partial Concatenated Match) SOS

Hello,

I'm running in circles. I've built this dashboard over a bunch of concatenated SKU placed in column. Each string vary the number of substrings, but every code has 34 lenght. A great human being also from this community helped me with this user defined function below. The code does exactly what I need: it loops through column "P" (SKUs) and search for partial matches within each code for every partial code that is referenced in another column.

For instance, in this other column I have the partial codes: "005P" 039P" 0154P"

The code will search for these 3 values within every substring of each cell of column "P", and then will return into another column ("M") the same array but showing only the match cases instead.


So if I had:

001M252P001C001T062D001E001S002993; 089M005P001C001T012D001E111S200326; 001M252P001C001T062D001E001S002993

It'd only return

89M005P001C001T012D001E111S200326;

The script would be perfect if it didn't consume too much processing. The file became useless because it takes too long to update. I've tried everything I could think of, I simplified the formulas, switched every vlookup for index, tried to be the less redundant as possible, but it's still too slow.

The way it is right now, I have these listboxes that will trigger a true/false statement that populates the reference column for the partial codes. The listboxes are set to trigger manual calculation as a band-aid, and when the user is done selecting the filters he presses a "update button" that turn calculation back to automatic. This band-aid is due to the fact that I assumed a conflict was happening when clicking the listbox and the user function would update at the same time the column is being populated.
My guess is that using a sub code could be more efficient and maybe save me sometime. Right now it takes about 15-20 min to update every time. If I could take it down to 5 min I'd be glad already.

Anyone has an opinion on this?

Function InstanceCount(ByVal StringToSearch As String, ByVal StringToFind As String) As Long
    If Len(StringToFind) Then
        InstanceCount = UBound(Split(StringToSearch, StringToFind))
    End If
End Function

Function FindandCopy(origData As Range) As String

Dim i As Long
Dim c As Range
Dim results As String

For Each c In Worksheets("Engine I").Range("G4:G121")
    
    If InstanceCount(origData, c.Value) > 0 Then
        
        For i = 1 To CLng(InstanceCount(origData, c.Value))
            results = Mid(origData, (InStr(origData, c.Value) - 0), 34) & "; " & results
        Next
    End If
    FindandCopy = results
Next c

End Function