locating #words in cells and counting frequency

[curious_dan]

I need some help.

I have 1000 rows of data from A1:A100 and in each cell a large amount of text appears in each cell. In amongst the text are hashtags (think of tweets on twitter for example). The hashtags could be located anywhere within the cell. Some will have them, some will not.

At this stage, I don't know what these hashtags are and I'm not looking to pull out specific ones. Instead I'm looking to pull them all out in one long list, with a count of how frequent each one occurs.

Any thoughts?

 

[FormR]

Hi, see if the code in post#2 here is of any help.

https://www.mrexcel.com/forum/excel...ecific-words-within-range-cells-possible.html

 

[curious_dan]

Hi, see if the code in post#2 here is of any help.

https://www.mrexcel.com/forum/excel...ecific-words-within-range-cells-possible.html

I had a feeling someone might've posed this question before. It's brilliant! Thank you very much!!!

 

[louisH]

Hi,

Happy to see you found your answer.
I was writing code but was too late so I'm still posting it here in case it's of any help :

Sub test()

    Dim dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
    
    Dim txt() As String
    
    For Each c In Range("A1:A100")
        
        txt = Split(c.Value, " ")
        For Each w In txt
        
            If InStr(w, "#") = 0 Then GoTo Skip
            
            If dic.Exists(w) Then
                dic(w) = dic(w) + 1
            Else
                dic.Add w, 1
            End If
Skip:
            
        Next w
        
    Next c
    
    For i = 0 To dic.Count - 1
        Range("B" & i + 1).Value = dic.keys()(i)
        Range("C" & i + 1).Value = dic.items()(i)
    Next i
    
    
End Sub

 

[curious_dan]

Hi,

Happy to see you found your answer.
I was writing code but was too late so I'm still posting it here in case it's of any help :

Sub test()

    Dim dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
    
    Dim txt() As String
    
    For Each c In Range("A1:A100")
        
        txt = Split(c.Value, " ")
        For Each w In txt
        
            If InStr(w, "#") = 0 Then GoTo Skip
            
            If dic.Exists(w) Then
                dic(w) = dic(w) + 1
            Else
                dic.Add w, 1
            End If
Skip:
            
        Next w
        
    Next c
    
    For i = 0 To dic.Count - 1
        Range("B" & i + 1).Value = dic.keys()(i)
        Range("C" & i + 1).Value = dic.items()(i)
    Next i
    
    
End Sub

Great, thanks Louis.