Transpose column and add separator column

Question

I'm trying to transpose a column from one sheet into a row of another sheet with a new blank column separating each result

=TRANSPOSE(Sheet1!A1:A30)

Whats the easist way to achieve this without having to add a blank row between each of the rows in the orginal sheet

Thanks

Answer 1

I think this may be the easiest way

split(textjoin("||",,Sheet1!A1:A30),"|",,false)

Answer 2

This answer is based on Toms answer:

split(textjoin("||",,Sheet1!A1:A30),"|",,false)

I like the solution because it is simple.

More general question would be:

How to add N extra separator columns with a formula

Here's the formula:

=TRANSPOSE(SPLIT(JOIN("|"&rept("|",1),A1:A30),"|",1,0))

where

• "|" is a rare char you do not have in your dataset
• rept("|",1) is to get N separator columns. Change 1 to N.

The only problem with the formula is join function limit on 50000 characters.

The final function won't give the error with a large dataset.

Please try:

=TRANSPOSE(ArrayFormula(TRIM(SPLIT(QUERY(A1:A30&"|"&rept("|",1),,2^99),"|",1,0))))

• query replaces join and have no limits
• trim is needed because query creates spaces at the end of each line.

Answer 3

Going further in depth on the issue above (question):

What would you write if you want a certain text for each new column:

E.g. I have several datasets (columns) with 1) drilling resistance and 2) associated depths, all of which I will extract from another sheet into this new one. I have a list of boreholenames which I will transpose and insert as text over the columns with 1).

Then I want to add a column for each borehole with the height (2). How do I then automatize adding text for each new column with the writing "height (m.a.s.l.) boreholenumber", where the latter could be just picked from the borehole name list.

And by the way. The split function doesn't exist in my excel-program :( How to I get it?