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

MiliJJ

New Member
Joined
Nov 30, 2012
Messages
5
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-ignore:padding; 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 AColumn BColumn 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>
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

hippiehacker

Well-known Member
Joined
Aug 2, 2011
Messages
1,911
this should do not the nicest code but...

Code:
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
 

mw00087

New Member
Joined
Aug 10, 2013
Messages
1
Guys, I have no idea where to put this code! I have to do this for work, and really don't have a clue! :(
 

bilbobaggins530

New Member
Joined
Apr 6, 2020
Messages
1
Office Version
2010
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,906
Messages
5,447,203
Members
405,442
Latest member
simonator

This Week's Hot Topics

Top