Extracting @mentions and #hashtags from column A to Columns B and C.

[MiliJJ]

I have a really large database of tweets. Most of the tweets have multiple #hashtags and @mentions. I want all the #hashtags separated with a space in one column and all the @mentions in another column. I already know how to extract the first occurrence of a #hashtag and a @mention. But I don't know to get them all? Some of the tweets have as much as 8 #hashtags. Manually going through the tweets and copy/pasting the #hashtags and @mentions seem an impossible task for over 5,000 tweets.

Here is an example of what I want. I have Column A and I want a macro that would populate columns B and C. (I'm on Windows &, Excel 2010)

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} .font5 {color:windowtext; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0;} .font6 {color:windowtext; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl64 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0;} .xl65 {color:windowtext; font-family:"Helvetica Neue"; mso-generic-font-family:auto; mso-font-charset:0;} .xl66 {color:windowtext; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0;} --> </style> Column A Column B Column C Dear <s style="text-decoration:initial">#</s>DavidStern, @spurs put a quality team on the floor and should have beat the @heat. Leave <s style="text-decoration:initial">#</s>Pop alone. <s style="text-decoration:initial">#</s>Spurs a classy organization. #DavidStern #Pop #Spurs @spurs @heat Live broadcast from @Nacho_xtreme: "Papelucho Radio"http://mixlr.com/nachoxtreme-radio … <s style="text-decoration:initial">#</s>mixlr <s style="text-decoration: initial">#</s>pop <s style="text-decoration:initial">#</s>dance #mixlr #pop #dance @Nacho_xtreme "Since You Left" by @EmilNow now playing on KGUP 106.5FM. Listen now on The Emerge Radio Networks <s style="text-decoration:initial">#</s>Pop <s style="text-decoration:initial">#</s>Rock #Pop #Rock @EmilNow Family Night <s style="text-decoration:initial">#</s>battleofthegenerations Dad has the <s style="text-decoration:initial">#</s>Monkeys Mom has <s style="text-decoration:initial">#</s>DonnieOsman @michaelbuble for me <s style="text-decoration:initial">#</s>Dubstep for the boys<s style="text-decoration:initial">#</s>Pop for sissy #battleofthegenerations #Monkeys #DonnieOsman #Dubstep #Pop @michaelbuble @McKinzeepowell @m0ore21 I love that the PNW and the Midwest are on the same page!! <s style="text-decoration:initial">#</s>Pop #pop @McKinzeepowell @m0ore21 <tbody> </tbody>

<tbody>
</tbody>

 

[hippiehacker]

this should do not the nicest code but...

Sub miliJJ()

lastrow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A1:A" & lastrow)
    Range(cell.Address).Offset(0, 2).Value = get_text(Range(cell.Address), "@")
    Range(cell.Address).Offset(0, 1).Value = get_text(Range(cell.Address), "#")
Next cell
End Sub

Function get_text(rng As Range, CHR As String)

Dim Text1 As String
Count1 = Len(rng) - Len(Replace(rng, CHR, ""))
Dim j As Long
j = 1
For i = 1 To Count1
sCh = InStr(j, rng, CHR)
eCh = InStr(InStr(j, rng, CHR), rng, " ")
If eCh = 0 Then eCh = Len(rng) + 1

If Text1 = "" Then
Text1 = Mid(rng, sCh, eCh - sCh) & " "
Else
Text1 = Text1 & " " & Mid(rng, sCh, eCh - sCh)
End If
j = j + eCh - j
Next i
get_text = Text1
End Function

 

[MiliJJ]

Thank you!

It worked like a charm!

 

[MiliJJ]

Thank you!

It worked like a charm!

 

[mw00087]

Guys, I have no idea where to put this code! I have to do this for work, and really don't have a clue!

 

[hippiehacker]

Guys, I have no idea where to put this code! I have to do this for work, and really don't have a clue!

the code needs to go into a module

 

[bilbobaggins530]

Thank you so much for this Macros! It's helped me out immensely. I was wondering if you could tell me what to do for tweets where there is no space between the hashtags or @handles. For example have have a tweet that just has #AltonSterling#PhilandoCastile and when the Macros gets to that line it gives me an error. Thank you so much for your help with this already. If you have any info on how I could fix this I'd be most grateful!
-Paige

 

[abbas50]

this should do not the nicest code but...

Sub miliJJ()

lastrow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A1:A" & lastrow)
    Range(cell.Address).Offset(0, 2).Value = get_text(Range(cell.Address), "@")
    Range(cell.Address).Offset(0, 1).Value = get_text(Range(cell.Address), "#")
Next cell
End Sub

Function get_text(rng As Range, CHR As String)

Dim Text1 As String
Count1 = Len(rng) - Len(Replace(rng, CHR, ""))
Dim j As Long
j = 1
For i = 1 To Count1
sCh = InStr(j, rng, CHR)
eCh = InStr(InStr(j, rng, CHR), rng, " ")
If eCh = 0 Then eCh = Len(rng) + 1

If Text1 = "" Then
Text1 = Mid(rng, sCh, eCh - sCh) & " "
Else
Text1 = Text1 & " " & Mid(rng, sCh, eCh - sCh)
End If
j = j + eCh - j
Next i
get_text = Text1
End Function

Hello i have tried your code it works until a line without a hashtag then it gives a debug :/ can you please fix that issue? much appreciated