So I have a long extracted list (5600+ somewhat rows), with two columns, where A1 matches B1 etc. In some rare cases (around 200) the cell has multiple data separated by line break, both in A and B. If if have two lines in A I have two (matching) lines in B. And it can be up to 7 lines in one cell.
I need to make this into a list, were A still matches B, but each cell only has 1 line. I have found a way to separate the multi lines (Text to Columns) with line break as separator, but that gives me several columns instead, and I need to do a lot of copy past, which is not durable in the long run since I do this each week.
Any idea how to solve with formulas?
Example:
From this:
A1
A2B1
B2A3 B3 A4 B4 A5
A6
A7B5
B6
B7A8
A9B8
B9
Into this:
A1 B1 A2 B2 A3 B3 A4 B4 A5 B5 A6 B6 A7 B7 A8 B8 A9 B9
Bookmarks