I wanted to explore a data-set in excel. I thought I would do that using Excel. The problem was that when I opened the data-set, data was in one column. It was “supposed” to be in different columns but no – I found that it was in one excel cell. This was not Excels fault – it was just the way the data-set was defined. Here’s what I mean:
Can you see that the TWO values are in ONE column?
Problem? Yes. How do we solve it? Turns out there’s a nice feature called “Text to Columns” that should be of help here. Let’s try that:
1) Excel Toolbar > Data > Data Tools > Text to columns
2) This should open the “convert text to columns wizard”
Step 1: I chose Delimited
Step 2: I chose Comma as the delimiter.
Here are other delimiters that you could choose:
Step 3: I left the default choices. But you could change the data format if you want. You could also choose the destination cells.
Clicked on FINISH
3) Nice! Here’s what I wanted – And I added a header row.
And my data exploration:
In this blog-post, we saw how one can split excel cell into separate columns at each comma, tab, space, semicolon or user-defined-character.