Table of Contents
This tip is about the how to Split One Column into Multiple Columns in Excel. So read this free guide, How to Split One Column into Multiple Columns in Excel step by step. If you have query related to same article you may contact us.
How to Split One Column into Multiple Columns in Excel – Guide
Splitting a very long column into multiple cells can make the difference between an easy-to-read Microsoft Excel document and a document with poorly structured data. In the latest versions of the software, you can split the data into a long column using the feature available in the “Data” menu. Depending on the size of your dataset, you can choose one or more delimiters to split the text in your column.
How to Split a column into multiple columns
Let’s say you have a list of names that you want to split into First Name and Last Name columns.
We now have two columns of data, with the first name in Column A and the last name in Column B. It is important to note that when you split the contents of a cell, Excel does not insert new cells to contain the contents. Thus, the new cells will replace the contents of the next cell to the right. So make sure you leave a trailing empty space before splitting. You also have the option to select the destination of the split data. You can specify this during Step 7 by typing where you want the split cells to appear in the target input box. You can also select the target cell here.
Needless to say, the number of columns your data will be split into depends on the delimiters you selected. That means if you have a comma as a delimiter and in some cells you have three words separated by commas, your data will be split into three columns.
How to Split multiple rows in one cell into multiple cells
Now let’s discuss how to go in cases where you have a lot of information provided on separate lines of a cell. Take for example the sheet below. Here you can see an entire address given in each cell. Each part of the address is on a separate line of a cell. Separating this column into four different columns that can show the person’s full name, Street, City, and Country, would make it much easier to identify patterns in the data. Unfortunately, separating cells with multiple rows is not as easy as the method given above. But it’s also not very difficult. See How to solve this problem.
Once this is done, you will find columns B to E, each containing an element separate from the address that was present in column A.
Before we end the article, we want to add one more case. You may have more than one merged cell and are looking for a way to unmerge or split those cells. We would also like to address this issue in case you came to our page looking for a solution to this. Here are the steps:
Final note
I hope you like the guide How to Split One Column into Multiple Columns in Excel. In case if you have any query regards this article you may ask us. Also, please share your love by sharing this article with your friends.