Splitting text in one column into multiple columns based on a deliminator or a fixed size.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
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.