In Microsoft Excel 2007, we can split the characters within a particular cell into different columns. It can be done in two ways:
• Fixed Width
• Delimited
Fixed width: This option is basically used when the characters in a particulars cell are equal in format.
Delimited: This is used basically when you need to split characters by way of any condition such as a symbol etc.
You will get a better clarity on both once we explain you taking an example.
Fixed width:
We will now take you through as to how we do fixed width text to column splitting.
Consider the below data that we are considering for our example.
Now in the above data we have a combined data available in one cell itself in different rows. We have No, Name and Place in one cell itself. Let us now see how we can split it into different columns.
Here you can see that all the data in different rows are of the same format i.e. the No is of 5 characters, Name of 4 characters and the last one doesn’t matter as it has to go into the last column. Therefore we will apply a fixed width here.
Select the entire data that needs to be split, point to Data and then point to “Text to Columns”
Once you click on “Text to Columns” a dialog box opens up, as shown below.
As you can see Delimited is selected by default. But here we need Fixed width. Click on "Fixed width" and click "Next".
Once you click "Next" you will be taken to the next step of “Convert Text to column wizard”.
Now just click on from where you need to split the cell to columns. Once you click you will be able to see arrows appearing.
Once done just click on "Next" and then click "Finish".
As soon as you click "Finish" the data gets split into different columns.
Here we split cells into columns by “Fixed width” option.
Delimited:
Now let us explain you how to splt cells to columns by “Delimited” option.
For this we will be considering the same data but let us change the size of the characters. This means Delimited is basically used when characters in a cell are not of the same size.
Consider the below.
Now follow the same step we did for “Fixed width”, instead just click on Delimited and click "Next". It takes you to as shown below:
Unselect the preselected “Tab” option and select “Other” and in the box provided type a hyphen (-) as shown below.
Now click on "Next" and then "Finish".
As soon as you click "Finish" the cells get split up into different columns. The only difference you will see here is that the hyphen (-) marks do not get split, however they are used as means to split.
That all with learning as to how we can split cells to columns using “Text to column” function in MS Excel 2007.
In order to join or combine the different columns into one cell you can use the “
Concatenate” function.
Tip: While doing “Fixed width” it is not necessary to bring arrows after each character. You can click only for one split portion and rest remains intact. And while doing “Delimited” you can use predefined options such as Tab, semi colon etc in case your text has those.
Now in the above data we have a combined data available in one cell itself in different rows. We have No, Name and Place in one cell itself. Let us now see how we can split it into different columns.
Here you can see that all the data in different rows are of the same format i.e. the No is of 5 characters, Name of 4 characters and the last one doesn’t matter as it has to go into the last column. Therefore we will apply a fixed width here.
Select the entire data that needs to be split, point to Data and then point to “Text to Columns”
Once you click on “Text to Columns” a dialog box opens up, as shown below.
As you can see Delimited is selected by default. But here we need Fixed width. Click on "Fixed width" and click "Next".
Once you click "Next" you will be taken to the next step of “Convert Text to column wizard”.
Now just click on from where you need to split the cell to columns. Once you click you will be able to see arrows appearing.
Once done just click on "Next" and then click "Finish".
As soon as you click "Finish" the data gets split into different columns.
Here we split cells into columns by “Fixed width” option.
Delimited:
Now let us explain you how to splt cells to columns by “Delimited” option.
For this we will be considering the same data but let us change the size of the characters. This means Delimited is basically used when characters in a cell are not of the same size.
Consider the below.
Now follow the same step we did for “Fixed width”, instead just click on Delimited and click "Next". It takes you to as shown below:
Unselect the preselected “Tab” option and select “Other” and in the box provided type a hyphen (-) as shown below.
Now click on "Next" and then "Finish".
As soon as you click "Finish" the cells get split up into different columns. The only difference you will see here is that the hyphen (-) marks do not get split, however they are used as means to split.
That all with learning as to how we can split cells to columns using “Text to column” function in MS Excel 2007.
In order to join or combine the different columns into one cell you can use the “Concatenate” function.
Tip: While doing “Fixed width” it is not necessary to bring arrows after each character. You can click only for one split portion and rest remains intact. And while doing “Delimited” you can use predefined options such as Tab, semi colon etc in case your text has those.
