Convert Text to Columns

Splitting text in one column into multiple columns based on a deliminator or a fixed size.

• 1:00
• Skill Level: 4
• What's my skill level
  

  All of our videos are listed by user Excel Skill Level.

  To find out your personal skill level, download our quick assessment.

  Once you've completed the test, send it to us and we'll evaluate for you.

  

• 

Convert Text to Columns in Excel

You’ll see there is lots of information  and there seem to be  commas that divide it. If you look at the cell you’ll see for whatever reason all the information is currently in one column and you want to convert text to columns.

What you need to do is somehow split it out and the information spreads itself evenly over the columns. There is a tool that can do this for you which is the Text  to Columns tool under the Data tab. So what you need to do is you highlight the cells you are looking at. Then you click Text to Columns. What you’ll see Excel does it then says ok – how do you want me to split up this column? Either delimited which means  that some sort of character is splitting it or fixed width  which is a fixed number of characters between each column.  To me this looks like it is delimited, and a comma is doing it so I’m going to click “delimited”  and go “next “– you’ll see at the moment I’ve got something ticked there, I’ll switch it off.

If I go to comma you’ll see what its done is its put a line in wherever its found a comma – you want to go down you’ll see that seems to be correct. The details seem to be coming in the correct  order.  I don’t want to accept it yet – I want to  go back and show you if I had “fixed width”  and I click “next” Excel then chooses for you but you can then move these around – you can say no,  the fixed widths are for example  happening there  or I can add a line etc. I am just going to go back. To me it is  quite clear these are delimited, click “next”, comma, looks about right. Whether you choose delimited or fixed width – in either case you’ll get to this screen and you are given the option to actually choose which columns will be imported.

So here you’ll say do not import and for example and it will say skip column. It will also give it a format – you can say day, year, month and you can actually point out to Excel exactly what the format of the date is or you can have it as text or general. There is also an advanced feature here in which you can tell it how the decimals are separated and how thousands ar separated. In either case once we have finished with that we can say “Finish”. You’ll see Excel has taken the single column and split it into columns which look a little bit more reasonable.