If you regularly use spreadsheet programs to process large datasets, you will welcome anything that helps. This is where drop-down lists in Excel come in. These allow you to conveniently add content with just one click so you don’t have to type the same thing again and again. We show you how to insert a drop-down in Excel.
The MS Word text editor gives you the ability to create tables in which you can then split a cell into one or more columns for content – unlike tables in Excel. The fact that Excel doesn’t offer this feature is because the spreadsheet would not be able to correctly process the entered values. So, if you need to split an Excel cell in order to present the content more effectively, you’ll need to resort to alternative solutions.
We show you how to split cells in Excelover multiple columns using separators and the text conversion assistant. What’s more, you’ll learn in our article how you can separate accidentally combined Excel cells that contain multiple values.
Tutorial: Splitting Excel Cells Over Multiple Columns
There can be a range of reasons for splitting a particular Excel column or dividing the values of certain cells in this column over multiple columns:
This splitting option is useful if your tables do not contain any special functions, but the content is too extensive for the layout of cells and columns used so far. Moreover, you may also want to divide an Excel cell if it contains two or more values that should be processed separately by Excel – this is often the case especially when working with imported datasets.
Sometimes, it’s also necessary to split Excel cells that have previously been combined.
How to Split Content of an Excel Cell Over Multiple Columns
If you have an Excel cell with content that you’d like to split over multiple cells, you can do this at any time. The only requirement is that there are enough free columns available (or cells if the division is only going to be applied to an individual cell). The number of free columns must always be equal to at least the number of values that are to be separated from the affected Excel cell. For instance, if you want to split content over three columns in total, you’ll need two free columns next to the first one.
In the following guide, we use a starting cell as an example that contains the three values “1”, “2”,and“3”, which have to be divided over three different columns. For the first step, you need to place separators between these three values. Tab stops, spaces, commas or semicolons (see our example) can be used for this purpose:
Select the cell and open the Data tab. Next, click on the button “Text to Columns”:
In the first step with the text conversion assistant, keep the option “Delimited” active or select it if it is not already, and click “Next”.
For the second step, place a tick in the box for the chosen separator. If you have opted for your own individual symbol, activate the box next to the entry “Other” and enter the character into the entry field. After that, click on “Next” again:
Finally, you can configure the data format of the columns; the “Standard” option is suitable in most cases. Once this has been selected, all date and numerical values will be kept unchanged, while all other values will be automatically converted to text. You can confirm the settings for dividing the content by clicking on “Finish”. Excel will then split the cell. The result looks like the following in our example:
Separating Combined Excel Cells: How it Works
If you want to separate Excel cells that contain more than one value and have been combined into a merged cell, the steps explained above will not work. This is because Excel can only convert one column at a time, while a merged cell always comprises at least two columns. But you can split the content among different cells by first undoing the merged cell as follows:
- Select the merged cell using the left mouse button.
- Open the “Home” tab.
- Click on the triangle symbol for the menu item “Merge & Center” (located in the “Alignment” section) and select the option “Unmerge Cells”.
Alternatively, you can unmerge the cell this way:
- Click on the merged cell using the right mouse button and select the option “Format Cells”.
- Remove the tick from the box next to “Merge Cells”.
- Click on “OK”.
Unmerging the cell will place all the content back into a single cell. However, you can use the conversion assistant as already shown in order to split the values back over multiple cells or columns.