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

0 votes

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?

Feb 21, 2023 in Others by Kithuzzz
• 38,010 points
364 views

1 answer to this question.

0 votes

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.

enter image description here

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,""))))))))
answered Feb 21, 2023 by narikkadan
• 63,700 points

Related Questions In Others

–2 votes
0 answers
+1 vote
1 answer

Excel or Google formula to count occurrences of an 8-digit number within a text string

To match an eight-digit number, you may ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 63,700 points
673 views
0 votes
1 answer

Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

answered Jan 9, 2023 in Others by narikkadan
• 63,700 points
435 views
0 votes
1 answer

Excel: Is it possible to reorder the data in 2 columns to match up if they have a certain number of characters / a string in common?

Try this: =LET(files,A1:A4, URLs,B1:B4, f,BYROW(files,LAMBDA(r,TEX ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,700 points
313 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
757 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,700 points
1,533 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,700 points
1,377 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,700 points
1,048 views
0 votes
1 answer
0 votes
1 answer

Excel SUMIFS() using string with greater/less than symbols

Put an = in front to let Excel know ...READ MORE

answered Nov 20, 2022 in Others by narikkadan
• 63,700 points
398 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP