Is there a way to reverse-text-to-columns in Google Sheets?

Question

I have n columns and m rows of cells.

1. I want to batch concatenate each cell in a row into a comma-separated entry:

   Topic 1, Topic 8, Topic 13, Topic n

2. Then, I want to batch concatenate each of these previously concatenated entries into one sequence of semi-colon-separated entries:

   Topic 1, Topic 8, Topic 13, Topic n; Topic 2, Topic 9, Topic 10, Topic n;....

3. I would like for the output to be a conversion of all n-columns and m-rows into text in one single cell, with the above format.

It's basically a reverse-text-to-columns that reduces a table to a single cell. I have tried doing this with CONCATENATE, but it doesn't scale for n-columns/m-rows, and I can't seem to find a Macro solution. I would greatly appreciate any help in this matter!

Answer 1

if your dataset is small you can use:

=TEXTJOIN(", "; 1; A:D)

if your dataset is huge you can do:

=ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(A:D="",,A:D&",")),,999^99)),,999^99))

tho empty cells may introduce unwanted multi-spacing so to counter it do:

=ARRAYFORMULA(QUERY(TRIM(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(A:D="",,"♦"&A:D&",")),,999^99)),,999^99), "♦"))),,999^99))

---

UPDATE:

=ARRAYFORMULA(QUERY(QUERY(REGEXREPLACE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(
 IF(A3:D="",, A3:D&",")),,999^99))&";"), ", ;|,;", ";"),
 "where not Col1 starts with ';'", 0),,999^99))