During a recent project I had to take a number of tables from a PDF into a spreadsheet. PDFs are great for viewing but with tables they don’t always preserve the structure. Unfortunately my spreadsheet software could not split the data at the end of the row, and the whole table I copied ended up getting pasted into one column.

There’s always someone who has been there done that, and its really easy to transform back into the correct table. Just plug the formula below into an empty cell, and note the two 5’s should match the number of columns the data originally had (so 5 for 5 cols, 3 for 3 cols etc). Then drag the formula to fill across the number of columns you have, and down the number of rows, et volia you’ll have the single column transform back into the original number of columns!

=INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1))