Split Cell content to rows

[Cunner]

Hello everyone,

I got a data download from a database with which I have to work. The data revolves around company alliances.
Unfortunately, within the excel-sheet I receive, information for an individual alliance is included in one row, meaning that the respective participant-information is condensed in one cell. There are also some cells whose content is not participant specific.
1. Is there a way to automatically split the information from the individual cells to different rows while
2. keeping (e.g. copy & paste) the content of some cells?

I have built a small example sheet for you to show the issue. I want the information in colums A, B, D and E to be split into two separate rows each and to keep column C for this.

Thanks a lot in advance and
kind regards

[6StringJazzer]

The question is not completely clear. It looks like your sample workbook shows what the data looks like to start, but not sure what result you want. Please see Sheet2 in the sample for what I think you are asking for. I can't figure out what you want to do with column C--what does this mean: "keep column C for this"

[Cunner]

Hi 6StringJazzer,
thanks a lot for the reply already. I have updated the example sheet.
In fact there are a couple of possible ways that are ok for me. One is as I have done it, i.e. just copy&paste the cell as I have done it. Otherwise the "second row" could stay empty or the cells could be merged across the two rows.

[6StringJazzer]

Here is a revision that updates the formulas to match what you showed. It also has an improved version of the formulas so they can by copied from any row to any row.

[Cunner]

Hi again.
I must admit I do not understand the workings of the code completely, yet. But I have the impression, that the code by default assumes that there are always "just" two different items in a cell. Is that right? Because there might be some cells where there are three...

[6StringJazzer]

You are correct, the formulas assume that there are two items per cell, because that's what the example showed.

To get a more robust solution you must give a very specific description of your data. Generalizing this solution for an arbitrary number of items per cell may be better implemented with a macro instead of formulas.

To give a quick explanation, the formula in the target row calculates the source row based on the target row number. Therefore the calculation for target 2 gives source 2, target 3 gives source 2, target 4 gives source 3, etc. Then if the target row is an even number, it uses LEFT to get the first part of the item up until it finds a line break, which is CHAR(10). If the target row number is odd, it uses RIGHT to get the last part of the item after the line break.

You can see that the algorithm above does not work if the cell does not always have two items. It could be adapted to work with three items, but if the cell can have either two or three (or more?) items, it get very unwieldy.