Splitting text in one column into multiple columns based on a deliminator or a fixed size.
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.
Advanced Excel Course – June 2013 in South Africa Our next advanced excel course is running during June 2013 in Johannesburg. During the week of the 10th June we offer the following options. You can either attend the full 5 … Continue reading
A very popular chart is the waterfall chart which shows the impact of various items on a total e.g. the various components that explain the move from budgeted profit to actual profit. A common way to build this is using … Continue reading
The remarkable thing about Adrian's presentation is his ability to further change and adapt the focus of the course as issues arose. He was also able to keep the more skilled delegates challenged...– Alexander Proudfoot Consultants