I was recently looking for a cheap and easy way to export a twitter hashtag to an Excel file. I wanted to be able to easily sort through all the users that had tweeted the hashtag and manipulate the data in Excel.
Below is the example. I wanted to have a nice Excel list of all the tweets with #messiahbusiness.
This is the final product that was pretty easy to get to. Check out the method below or download the sample spreadsheet template.
- Do a simple select, copy, and past to Excel from your Twitter list (paste special as text only).
- Find where the @ character is used in the username
- Return all text after the twitter name (where B3 points to step 2 above and 20 is just a large number of characters to return so the entire twitter name is included)
- Find where the space is located after the twitter name (where C3 points to step 3 above)
- =FIND(” “,C3)
- Return the cleaned up username (where C3 points to step 3 and D3 points to step 4)
- Method 1 can be condensed into one formula
- =MID(MID(A3,FIND(“@”,A3),20),1,FIND(” “,MID(A3,FIND(“@”,A3),20)))
- Make another formula to reference the tweet content to get everything on the same line
- Copy the columns with the cleaned up username and tweet content and paste special as values to another set of columns (this keeps the values and gets rid of all the formulas and references).
- Filter the newly created two columns to only show the twitternames and tweet content.
That’s it, feel free to download the sample spreadsheet template and use it for yourself.