HiddenText Ltd

Has anyone ever put you in the TO: address bar instead of the BCC: field and you end up with a ton of email addresses staring at you? Feel like your privacy was affected somehow? Well it was… this is what can happen when you tally that data up.

All you need is Excel !

1. Open a blank sheet
2. Paste in all the addresses
3. Find and replace on A1 “; ” with “;” – this will get rid of spaces after the semi-colons
4. Do a Text to Column on this data using Delimited Text code of ; – this will split out the addresses to unique cells
5. Copy these and move to A2, Paste Special – Transpose – this will now create a vertical list of unique addresses
6. Don’t forget to get rid of that first cell
7. Now do a Text to Column on this data using Delimited Text code of @ – splits domains from the name
8. Insert 2 columns at B and C – this is to allow room for names to be split out
9. Select Column A and Text to Column on . – this splits names out
10. Select Column D and Text to Column on . – this splits out domain names
11. Resize the columns
12. Now you need to get a bit clever with your fields:
    1. J will be the country
    2. I the department
    3. H the organisation
    4. G the company
13. Sort on H and move any data you find in H to the above columns
14. Sort on G and move any data you find in G to the above columns
15. Sort on F and move any data you find in F to the above columns
16. Sort on E and move any data you find in E to the above columns
17. Sort on D and move any data you find in D to the above columns
18. D, E and F should now be empty so can be deleted
19. Select all data, Sort (adding levels for) G,F,E,D,A
20. Insert a Row at the top
21. Title A with Name (and merge with A,B and C, and Left justify)
22. Title D with Company
23. Title E with Department
24. Title F with Organisation
25. Title G with Country
26. Select all the data and put boxes
27. Resize the text in Row 1 and make the font 16
28. Go to Page layout and Print Titles selecting 1…1
29. Go to View, Page Break Layout and move any vertical page breaks so it fits all data on one page wide (and a lot of pages long)
30. Save…
31. Yeah – look at it !! You have a list of names of people sorted by company !

You should end up with something like this :

Add-on work you could do:

1. Remove at all the personal accounts, AOL, Yahoo, Hotmail, Gmail
2. Insert the email addresses back in
3. Recombine the domains and create URLs
4. Send your spreadsheet back with a message to say please learn (feel free to link to this blog)

It appears I have been invited to an event, I’m one of 483 invites actually … some interesting people – I might just have to attend, the question is… do I take this list with me ?