# How to Split One Column into Multiple Columns in Excel **URL:** https://www.compsmag.com/blogs/how-to-split-one-column-into-multiple-columns-in-excel/ **Author:** Ayushi Chauhan **Published:** 2022-01-12 **Updated:** 2022-01-12 **Categories:** Blogs **Tags:** Blogs Daily, guiderobert, Guides and Tutorials, guiding12, How To Guide, How To Guides, Software tips and tutorials, Tips, Tricks **Reading Time:** 6 min --- 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. Select the column you want to split In the data ribbon, select "Text to Columns" (in the Data Tools group). This will open the Convert Text to Columns wizard. Here you will see an option that allows you to define how you want the data in the selected cells to be delimited. Make sure this option is selected. If you're not familiar with the term 'delimited', it's the character that specifies how the data in the cells are separated from each other, for example, the first and last name in each cell is separated by a space. This means that the delimiter here is a space character. Click Next By default you will find the tab delimiter checked. But we don't want to use it. We want to use the space delimiter. So uncheck the tab delimiter and check the Space option. There's also a checkbox that lets you specify whether you want to treat consecutive delimiters as one. Does this mean that if you have two spaces between names by mistake, you want to treat them as one space? You can see how your data will look after splitting in the data preview area at the bottom of the dialog. Note that when the space option is checked, we get exactly the result we want. Finally, click Next. Now you will see an option where you can specify the format of the data in the columns. By default, the General option is selected, which ensures that the columns have the same format as the original cells. Leave it with the General option selected and click Finish. 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. Select the column you want to split In the data ribbon, select "Text to Columns" (in the Data Tools group). This will open the Convert Text to Columns wizard. Here you will see an option that allows you to define how you want the data in the selected cells to be delimited. Make sure this option is selected. Click Next By default you will find the tab delimiter checked. Uncheck all marked delimiters and select the 'Other' option. In the little box next to this option, you need to specify the delimiter character you want to use. If you want to specify a line break character. Press Ctrl + J on the keyboard. This will show a small flashing dot inside the box. This means that the line break delimiter has been entered. Click Next. Now you will see an option where you can specify the format of the data in the columns. By default, the General option is selected, which ensures that the columns have the same format as the original cells. Here we also want all columns to appear from column B onwards so that the new cells do not overwrite the existing column. Next to the Destination, we see the cell '$A$2' written. We can change this by selecting our required target cell '$B$2' and clicking Finish. You may get a dialog asking if you want to overwrite the data that is already present in the target cells. Click OK. 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. ## How to Share up a merged cell 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: Click on the merged cell. If there is more than one cell that you want to unmerge, select all of them. Under Home Tab Alignment Tools, you'll see a dropdown next to the option that says Merge and Center. Click on the drop-down arrow and select “Unmerge Cells”. This will split the merged cell back to the original number of cells. ## 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. --- *End of Article*