How to combine data and then separate concatenated data?-CodePudding

Home > Back-end > How to combine data and then separate concatenated data?

How to combine data and then separate concatenated data?

Time：08-30

I would like to combine a range of data into one single cell G5.

Then I want to split Value G5 into original form. Thank you so much.

CodePudding user response：

ER1:

=ARRAYFORMULA(TRIM(REGEXREPLACE(QUERY(FLATTEN(QUERY(TRANSPOSE(
 {A1:B3&" |", C1:C3}),,9^9))&"♦",,9^9), "♦ |♦$", CHAR(10))))

ER2:

=ARRAYFORMULA(TRIM(SPLIT(FLATTEN(SPLIT(G5, CHAR(10))), "|")))