Practicum: Cleaning Twitter Searches

I'm ecstatic to be able assist a little bit with the Social Capital Markets 2009 conference, particularly in community building.  One of the tasks I'm working on involves cleaning up lists of people who are Tweeting.

If you've got a tool that does this already, good god, please let me know!  But if you're like me and after some fruitless searching decided that misusing excel is always fun, here's an algorithm to follow:

1. Search! (In my case, I search for #socap09: http://search.twitter.com/search?q=%23socap09 )
2. Copy and paste the results into an excel spreadsheet. It will look dreadful, but don't worry.
3. Sort on Column A
4. On the DATA menu, choose "Text to Column...", choose to use delimiters, and then choose "space" and "other", where other is a colon (:)
5. Sort on Column A
6. There will be some number with a blank first cell. Select those blank cells (not the whole column!)  On the EDIT menu, choose "Delete," and remove this first cell and choose "shift left."
7. Sort on Column A
8. Delete everything except column A. This will leave you with a list of TwitterIDs plus some dreck.
9. Delete those things that are obviously not TwitterIDs, such as *, # and actual numbers.
10. Again, choose text to column, using the delimiter "other" and chosing the underscore ( _ )
11. You've now broken apart the picture file names into separate columns. TwitterIDs only need one column (usually, exception below). Sort on Column B
12. The top results will almost all be file names for pictures, and the last word in the last column wll be "normal". The exception will be when someone uses an underscore in their TwitterID (such as the_real_shaq). There typically aren't many of those people compared to people with pictures; so scan and find those. Put the correct TwitterID in column A.  (NOTE: finding those TwitterIDs with an underscore is the only manual process. If you can think of a way to do that in an automated way on excel, let me know.)
13. Sort on Column B
14. Remove everything with something in Column B (because you've rescued the real TwitterIDs)
15. Go to the DATA menu and select PivotTable Report... and run this report. On the last step of the wizard, select "Layout," and drag whatever is available (should be a Twitter ID, in cell A1) to the side column of the pivot table and again into the center field of the report. Finish the pivot report.
16. Copy the left column of the pivot table and from the EDIT menu, choose "Paste Special..." and select values.

Voila - a clean list.