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.
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.
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 checkmark 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:
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:
Alternatively, you can unmerge the cell this way:
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.
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.
Drop-down lists allow you to configure tables in Excel in a way that is more professional, easier and less prone to errors. However, like anything else in day-to-day work, drop-down menus also have to be changed now and again. For this reason, we are going to explain how to edit Excel drop-down lists so you can adapt their content and appearance and add useful information for other users.
Excel cells often contain only a few numerical values or short text. But when you’re dealing with lengthy contents, the Microsoft application often hides a portion of the information. You can prevent this by wrapping text, which Excel allows you to do in two different ways. In this article, you’ll learn everything you need to know about this feature.
Provide powerful and reliable service to your clients with a web hosting package from IONOS.
View packages
