Dear Spreadsheet Whisperer: HALP! Unusable Email List Format!

Dear Spreadsheet Whisperer,

I am working with a political mobilization org that shared their email list with me so that I could engage their members in the upcoming election. Time to get involved! However, when opened the file with the list of emails, the spreadsheet was oriented landscape style and the emails looked like they were written in Wingdings font. I will need to retype and reformat all of this – it will take hours! Can you help?

From, HALP!

---

Dear HALP,

Thank you for reaching out to me! While I’m on family leave (!!!) I’ve been focusing my brain on entirely different types of data (future blog post??), and itching to fix spreadsheets again. So I’m absolutely delighted that you identified that your problem was solvable (without the brute force method); it takes a lot of humility to ask for help. Hearing from you made my day! For anyone reading this – you TOO could make my day by asking me to help you untangle your spreadsheets!

I spent some time giving your spreadsheet some TLC and I’m going to write out the steps I took in case this helps you or other activists in our overlapping communities!

step 1

I noticed that the spreadsheet had weird linebreaks and were not organized in a format where one piece of information was stored in one cell (ie Name | Email | Affiliation). It was like a run-on sentence split into a random number of rows, lacking expected punctuation. Instead of trying to figure out the spacing myself, I decided to smoosh the data into one long line of text so that I could use automation to split it into its component parts in a more logical and automated way. The formula for this is =Concatenate(). In a clean tab in the spreadsheet, typed =Concatenate( and then I dragged my cursor to select all of the gobbly goop and closed the parentheses. Now, I had one long line of gobbly boop instead of many rows of it. This might seem worse, but it’s actually a much better format to work with for…

step 2

I know that spreadsheets can “split text into columns” based on certain patterns. In this case, I noticed that your data had something called “HTML tags” attached to it, which is why it looked like Wingdings to us! The tag “&gt;;” means “>” (that symbol) and indicates the end of an email address, the way they sometimes appear when we copy and paste them. For example, the original source data might have looked like: Harriet Tubman <harriet.tubman@undergroundrr.org>. At some point when it was copy/pasted, emailed, or otherwise converted, the < > symbols were replaced with HTML tags. Now, we can use that repeating pattern to deduce the end of an email, at which point we want to split the gobbly goop into adjacent cells.

The tool for this in Google Sheets is under the “Data” tab > “split text to columns” > custom, and then I typed in the recurring text to look for: “&gt;;”. You need to work quickly after you select “split text to columns” because it will “infer” the pattern to use for splitting up the data and then the menu disappears! So click down to “custom” as quickly as you can.

As a result, we got a very long horizontal spreadsheet of cells! This is basically unusable in it’s current form, but perfectly set up for…

step 3

I know that I can change the orientation of data using a feature called “transpose” or “transform.” So, I used my cursor to copy all of the data in it’s mile-long horizontal shape, and then I “copied” the data. Next, I right-clicked and found “Paste Special” > Transposed. Now I have a vertical array of data-in-cells instead of a horizontal one! We’re starting to see data that we can use and organize! But, we’re not there yet. Stick with me for…

step 4

Your original data included contact names (First + Last Name) and Email; in our current format, all of this was in one cell per person. This would make it very difficult/annoying to use just the emails for outreach purposes. What do you think we should do?

If you guessed, “split text to columns” again – then you’re right! That’s exactly what I did. But this time, I used the HTML code that signaled the start of an email address, which in this case was the symbols, “&lt;”. Now are data are organized like this:

Full Name	Email
Harriet Tubman	harriet.tubman@undergroundrr.org

We are getting much closer to the finish line here, but there’s still…

step 5

I skimmed the newly formatted spreadsheet and took a sigh of relief. However, as I scrolled through, I noticed that there were some irregularities that still need to be worked out. Unfortunately, I didn’t find enough patterns to automate this. There were some emails that didn’t have < or > in their original raw source, so the way I split the data didn’t work and they were connected to someone else’s name. There were also a few typos, names without emails, or other errors that I just caught with my eyes. Maybe someone with more advanced skills than me could have resolved these errors in a more efficient way!

I’ll let you in on a little formula that I use to guide myself when there are manual tasks to be completed. If the task involves repetition 30 times or fewer, I just do it by hand. If the task requires more than 30, it might be worth fiddling around for a better option. There were fewer than 30 irregularities, so I just fixed them by hand.

Now is the part of the “cooking show” where I take the completed recipe out of the oven and take a bite. (BTW- I heard that Rachel Ray sustained mouth burns from sampling her actually fresh out of the oven, steaming hot creations! Try not to burn yourself on your data!). I sent you back a beautifully formatted spreadsheet with emails in one column and names in the other. Now, you can reach out to these wonderful individuals to engage them in electoral volunteer work without breaking into a cold sweat about data problems. And maybe next time… if there is a next time… you can follow these steps to clean up the data on your own. If not, I’ll be just a text/email/contact form away!

xoxo, Spreadsheet Whisperer