Results 1 to 6 of 6

Thread: Extracting @mentions and #hashtags from column A to Columns B and C.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2012
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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)

    Column A Column B Column C
    Dear #DavidStern, @spurs put a quality team on the floor and should have beat the @heat. Leave #Pop alone. #Spurs a classy organization. #DavidStern #Pop #Spurs @spurs @heat
    Live broadcast from @Nacho_xtreme: "Papelucho Radio"http://mixlr.com/nachoxtreme-radio #mixlr #pop #dance #mixlr #pop #dance @Nacho_xtreme
    "Since You Left" by @EmilNow now playing on KGUP 106.5FM. Listen now on The Emerge Radio Networks #Pop #Rock #Pop #Rock @EmilNow
    Family Night #battleofthegenerations Dad has the #Monkeys Mom has #DonnieOsman @michaelbuble for me #Dubstep for the boys#Pop for sissy #battleofthegenerations #Monkeys #DonnieOsman #Dubstep #Pop @michaelbuble
    @McKinzeepowell @m0ore21 I love that the PNW and the Midwest are on the same page!! #Pop #pop @McKinzeepowell @m0ore21

  2. #2
    Board Regular
    Join Date
    Aug 2011
    Location
    AMS
    Posts
    1,911
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  3. #3
    New Member
    Join Date
    Nov 2012
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thank you!

    It worked like a charm!

  4. #4
    New Member
    Join Date
    Nov 2012
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thank you!

    It worked like a charm!

  5. #5
    New Member
    Join Date
    Aug 2013
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  6. #6
    Board Regular
    Join Date
    Aug 2011
    Location
    AMS
    Posts
    1,911
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by mw00087 View Post
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •