Excel: How to split the content of one excel cell into separate columns?

Standard

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:

open a data set in excel text to columns

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

excel text to columns data tools

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:

split an excel columns tab semicolon comma space user defined

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.

excel an excel value cell splitted into seperate columns by comma

And my data exploration:

step one for building a predictive model that is data exploration

Conclusion:

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.

One thought on “Excel: How to split the content of one excel cell into separate columns?

What do you think? Leave a comment below.