Count duplicate words within string and remove if it occurs less than 10 times

Question

I'm new to VBA and I've written a code to remove duplicate words in a string. However, I'd like to be able to count  the occurrence of words within a string and if it occurs less than 10 times I want to delete that word.

This is my code:

Function RemoveDupeWords(text As String, Optional delimiter As String = " ") As String

    Dim dictionary  As Object
    Dim i, part
    
    Set dictionary = CreateObject("Scripting.Dictionary")
    dictionary.comparemode = vbTextCompare
    
    For Each i In Split(text, delimiter)
        part = Trim(i)
        If part <> "" And Not dictionary.Exists(part) Then
            dictionary.Add part, Nothing
        End If
    Next
    
    If dictionary.Count > 0 Then
        RemoveDupeWords = Join(dictionary.keys, delimiter)
    Else
        RemoveDupeWords = ""
    End If
    
    Set dictionary = Nothing
End Function

How can I do this?

Answer 1

Without some sample data, it's challenging to respond to this. I can only assume that you would wish to claim that substrings in the original place are maintained. If you're willing to use Microsoft Office 365, you could develop a function.

Formula in C1:

=LET(del," ",MAP(A1:A4,LAMBDA(x,LET(y,TEXTSPLIT(x,del),TEXTJOIN(del,,MAP(y,LAMBDA(z,IF(SUM(--(y=z))>9,z,""))))))))