Monday, February 7, 2011

by Jeff Spisak

So you've run a campaign and everything went swimmingly. Now you are ready to extract your data and review your findings. There's just one problem: Excel pulls all your particular data and lumps it all in the same column. Isn't there a way to make it more readable?

Take heart! The solution is simple and painless. First, let's take a look at your spreadsheet. Your exported data will show up in a column titled "Extra Data," where each piece of data that was formerly in its own column now occupies the same column, separated by commas. "Null" indicates the end of the list.

So let's get this cleared up, and fast! First click the letter of the column containing the extra data ("P" in our example), and then choose the Text to Columns option in the Data menu. Depending on your version of Excel, this option will either be in a drop-down menu when you click Data, or else it will appear horizontally under the Data tab (as it does in this example).

Now when we click the Text to Column button, it opens up the Conversion Wizard. Most of the default options are the ones we want anyway, so just click Next for Step 1.

Now we get to Step 2, and we need to make one important change. Make sure that you change the Delimiter option. The default choice is "Tab," but we want "Comma," so that Excel will make a new column each time it sees a comma (see, the engineers didn't put the commas in there by accident). So check Comma and click Next.

Step 3 looks intimidating! So we ignore it and click Finish.

Um, not sure why we see this. Just click OK.

And bingo! Everywhere we saw a comma, we now see a new column! Of course, you'll probably have to rename "Extra Data" to something like "First Name," then rename all the other columns so it makes sense, but that's that!

And that's all there is to it!