Divide Excel Cells (Table of Contents)
Divide Excel Cell into Multiple Columns
One such data structure is text file data when the data is copied from a text file to excel we usually get all the columns data in a single cell itself. In these cases, we need to divide the excel cell into multiple columns and that is the tricky process. In this article, we will take you through the process of dividing the cell in excel.
Data Structure in Text or CSV File
99% of the time when you get the data from excel there will be a common column separator symbol or delimiter which is common across columns.
For example, look at the below data in a text file.
As you can see above we have data structured in such a way where we cannot rectify how many columns are there in this. When we copy and paste the data to excel we get the pasted data as shown below.
We have got all the columns in a single cell itself. One of the identities we can see in the above data is we have a common delimiter between columns as comma (,). So using this common delimiter we can extract each column data to different columns like the below.
So to divide cell data into multiple cells we need to follow the “Text to Column” method in excel. Follow the below steps to divide an excel cell.
How to Divide a Cell in Excel?
Below are the examples to divide a cell in excel.
Example #1 – Text to Column Approach to Divide Cell
When we copy the data from text or CSV file we get the data in a single column only i.e. every column accommodated in a single cell with each column data separated by a common delimiter.
For example in the above case, “Comma” (,) was the common delimiter for each column, using this common delimiter we can divide the data into multiple excel cells.
Step 1: First copy the data from a text file to an excel file.
Step 2: Select the data column in the excel worksheet.
Step 3: Go to the DATA tab and click on “Text to Column”.
Step 4: Now it will open up the “Text to Column” window for you and it looks exactly like the below image.
Note: If you are a shortcut user then you can simply press ALT + A + E to open the above the window.
Step 5: Choose the “Delimited” and click on the “Next” option.
Step 6: In the next window we need to choose what the common delimiter which separates each column is. We have some pre-determined list in this and comma (,) is one of them, choose this then click on “Next”.
As you can see above as soon as you select the delimiter as comma we can already see the data preview showing each column separately.
Step 7: In the next window choose the “Destination” cell as the A1 cell and click on “Finish”.
Step 8: We will have a cell divided into multiple columns.
Now you can delete the second row which is unwanted.
Example #2 – Divide Cell With Not Determined Delimiter
We have seen how to divide excel cells with common delimiter with pre-determined value. Now we will see how to divide excel cells with no pre-determined delimiter.
For the same data, we have a different delimiter symbol “^”.
As usual open the “Text to Column” option and in the second step, we need to choose the delimiter which separates columns. Since we don’t have a pre-determined value here check the option “Other” and enter the delimiter character in the box.
That’s all, click on “Next” and click on “Finish” to divide the cells.
Now we will have a cell divided into multiple columns.
Things to Remember
- Dividing the cell is possible only if the column delimiter is common for all the columns.
- If the delimiter character is not pre-determined then we need to enter the delimiter character in the “Other” checkbox.
This has been a guide to Divide Cells in Excel. Here we discuss how to divide an excel cell into multiple columns along with examples 1) With delimiter and 2) Without delimiter. You can learn more about excel from the following articles –