How to Split One Column into Multiple Columns in Excel – Guide

If you have a long column of data that you want to split into multiple cells, you can do so using the “Data” menu in Microsoft Excel. This feature allows you to choose one or more delimiters to split the text in your column. Depending on the size of your dataset, you can choose either a comma or a space as your delimiter. ..

How to Split a column into multiple columns

You can create a custom column called “First Name” and a custom column called “Last Name” in your table. The First Name column will have the first name of the person’s first name, and the Last Name column will have the last name of the person’s last name.

  1. What is the main point of the article?
  2. What are the most important facts about the topic?
  3. How does this relate to other news stories?
  4. Why is this important? The main point of this article is that a new study has found that people who eat a Mediterranean-style diet are less likely to develop heart disease or stroke. The most important facts about this study are that it was conducted in Spain and Italy, two countries with a high prevalence of Mediterranean-style diets, and that it used data from more than 100,000 people. This information is relevant because it suggests that eating a Mediterranean-style diet may be an effective way to reduce your risk of developing heart disease or stroke. ..

On the first page of the wizard, select the column you want to convert. On the second page of the wizard, select how you want to format your data. You can choose between two formats: Text and Number. If you choose Text, then on the next page of the wizard, you will need to enter your data into a text box. After you have entered your data, click on the “Convert” button to convert your text into a column of numbers. ..

You can choose to delimit the data in the cells by using a space character or a delimiter.

By default, the tab character is used to separate paragraphs in a document. But we don’t want to use it. We want to use the space character instead. So uncheck the tab character and check the Space option. ..

Do you want to treat consecutive delimiters as one space?

You can see how your data will look after splitting in the data preview area at the bottom of the dialog. If you check the space option, we get exactly the result we want.

You can now 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. ..

  1. What is the main point of the article?
  2. What are the most important facts about the topic?
  3. How does this relate to other news stories?
  4. Why is this important? The main point of this article is that a new study has found that people who eat a Mediterranean-style diet are less likely to develop heart disease or stroke. The most important facts about this study are that it was conducted in Spain and Italy, two countries with a high prevalence of Mediterranean-style diets, and that it used data from more than 100,000 people. This information is relevant because it suggests that eating a Mediterranean-style diet may be an effective way to reduce your risk of developing heart disease or stroke. ..

In the wizard, select the text you want to convert and click on the “Convert” button.

You can define how the data in the selected cells will be delimited by choosing this option.

By default, you will find the line break character 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.

You can now 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 want all columns to appear from column A 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 be asked to overwrite the data that is already present in the target cells if you want to change or add new data. Click OK to proceed.

Column B: BEGIN Column E: ELEMENT 1: ELEMENT 2: END

How to Share up a merged cell

If you have merged cells in Excel, you may need to unmerge them or split them. Here are the steps: ..

Select all of the cells in the table. On the Home tab, in the Editing group, click Unmerge Cells. If there is more than one cell that you want to unmerge, select them all. ..

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

This guide will show you how to split one column into multiple columns in Excel. If you have any questions about this article, please feel free to ask us. Additionally, please share this article with your friends if you enjoyed it! ..