Tech Support Guy banner
Status
Not open for further replies.
1 - 7 of 7 Posts

·
Registered
Joined
·
4 Posts
Discussion Starter · #1 ·
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!
 

·
Registered
Joined
·
5,459 Posts
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?
 

·
Registered
Joined
·
4 Posts
Discussion Starter · #3 ·
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).
 

·
Registered
Joined
·
5,459 Posts
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?
 

·
Registered
Joined
·
4 Posts
Discussion Starter · #5 ·
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.
 

·
Registered
Joined
·
5,459 Posts
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.
 
1 - 7 of 7 Posts
Status
Not open for further replies.
Top