1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel: Twitter text-to-column Help

Discussion in 'Business Applications' started by Palmsie, Jan 12, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Palmsie

    Palmsie Thread Starter

    Joined:
    Jan 12, 2011
    Messages:
    4
    Hi everyone!
    Great website here, I get a lot of good answers from other posts. And I apologize in advance if this question has been asked (yes I did search around).

    I have a set of twitter data in excel that needs separating. The data are currently in 3 columns: Twitter user, Keyword, and Content. The content consists of the entire tweet.

    I have two goals. First, make a command or macro of some sort that will search the Content column find any username after the word "RT @" or "RT: @" (standing for Retweet). For example, RT: @TechSupport: Thanks for your awesome help!. I want to be able to copy the target of the retweet to a separate column. I don't need the actual message. I only need to know who the user in the first column is retweeting in the Content column.

    The next goal is similar. Some tweets contain hashtags like #TechSupportGuy. For example: "This website sure does have some helpful people. #TechSupportGuy". I need to copy these also to a separate column.

    Thanks again for your help here!
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there, welcome to the board!

    Can you give a little background information as to the scope of this project? Will this be for a single file only? To be used on one machine only? What version are you running in, or what is the lowest version this will be run in? How do you want to trigger these? Like a button, command button, toolbar, etc. Is there any way you can give us specifics on where your data is housed?
     
  3. Palmsie

    Palmsie Thread Starter

    Joined:
    Jan 12, 2011
    Messages:
    4
    Hi there! Yeah sorry about that...

    This is a single xlsx file on one box. I have exactly one file where I have previously collected around 4500 tweets.
    As far as how I actually accomplish this (command, macro, etc), I have no preference. As long as the end product is the same.

    Essentially the goal is to take a tweet and copy the nickname of anyone that is retweeted in the original tweet into an adjacent cell (see the OP for spreadsheet format). I've searched around the internets this evening and the current command that I've found that helps the most is this:
    =LEFT(MID(D6,FIND("@",D6)+1,LEN(D6)),FIND(" ",MID(D6,FIND("@",D6)+2,LEN(D6))))

    A sidenote of twitter is that when people retweet others they use an @ symbol. So if someone retweeted me it would be "RT @Palmsie: Hello there!" This generally does what I want for it to do. It searches a cell for the @ symbol and copies into the adjacent cell everything from @ to " " (a space); which, in this case is the nickname. This works well with extracting the first retweeted person in the tweet but in the event that the original post has several retweets, it doesn't extract subsequent RTs.

    For example:
    "RT @KanyeWest: Hold on imma let you finish! RT @TS: You're so mean!"

    The above command line would only return the word KanyeWest into the subsequent cell. I need both KanyeWest and TS to be copied to the cell (either together in the adjacent cell or in their own cells, next to one another).
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I would probably go for a UDF, personally. A code written formula which you can use in your worksheet. It would require you to convert your xlsx file into either an xlsm or xlsb. The xlsm is a macro-enabled file, but it's benefit is it still uses the Open XML file format, whereas the xlsb is a binary file format, accepts macros, even though it's not in the new Open XML format it tends to be a tighter compression, so smaller files.

    I'm thinking something like this for a formula and syntax:

    =GETRT( Cell , RT_num )

    This would allow you to parse multiple retweets into different cells dependent on the second syntax in the formula. Of course there are other ways to do it if you wanted to, this is just the first one that popped in my head.

    Of course another option would be to run this once on your worksheet. Probably select all of the cells you want this to run on. Assuming your Content is in column C starting at C2, you could have the RTs go into columns D, and any additionals go into the columns right of it. So two RT's would be in D2 and E2 respectively.

    Which would you prefer? Or were you wanting something completely different?
     
  5. Palmsie

    Palmsie Thread Starter

    Joined:
    Jan 12, 2011
    Messages:
    4
    Exactly. So if the original tweet is in column C, if the command finds 1 retweeted user the username is copied to D2; if 2 users are found it copies the [email protected] then E2 and so on. As for file formats, converting the file isn't a problem since this is the only one of its kind. In other words, there aren't any databases involves or multiple programs or any of that sort. This is a small task but (imho) an important one for my work.

    I'll look into the command you posted before.
     
  6. Palmsie

    Palmsie Thread Starter

    Joined:
    Jan 12, 2011
    Messages:
    4
    Any luck? I've been scouring the internet and other places, to no avail.
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi, I've been sick, sorry for being away. Which of the two I described would you prefer? A function (UDF) that works like a worksheet function? Or something which runs on the range automatically, one time? Or something else? Two very different solutions.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/974270

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice