Scrabble hooks summary

I could not follow all of your logic. What I could follow:

L2 looks like a simple SUBSTITUTE() function (help file: https://support.office.com/en-us/art...0-1e58df3bc332 )
=SUBSTITUTE(D2,K2,"",1). M2 looks like a similar SUBSTITUTE() function
L3 =D2 and L4=A2. M3 and M4 are similar.

Is there more to this than that?

I'm not sure I understand your concern. I do not understand why you had your results in L2:M4, because arranging the results in that way does make it difficult to copy the formulas down for all 5000+ rows. If you arranged the results in a row (L2=SUBSTITUTE(...), M2=D2, N2=A2, O2=SUBSTITUTE(...), P2=E2, Q2=A2), then those formulas could easily be copied down to perform the same calculation on each row.) You can see how I did this in S2:Y41 of this copy of your spreadsheet. If I have correctly understood the logic you want to apply to your analysis, then this can work without using VBA.

If you decide that VBA is needed for the analysis (or just prefer to do it in VBA), the substitute function is available to use in VBA (https://msdn.microsoft.com/en-us/lib...ffice.14).aspx )